' Define the Input Range

Set IRange = Range("A1").Resize(FinalRow, NextCol - 2)

' Do the Advanced Filter to get unique list of customers & product IRange.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=ORange, Unique:=True

' Determine how many unique rows we have LastRow = Cells(65536, NextCol).End(xlUp).Row

' Sort the data

Cells(1, NextCol).Resize(LastRow, 2).Sort Key1:=Cells(1, NextCol), _ Order1:=xlAscending, Key2:=Cells(1, NextCol + 1), _ Order2:=xlAscending, Header:=xlYes

End Sub

In the result shown in Figure 11.7, you can see that customer BCD buys only one product, and customer CDE buys three products. This might be useful to use as a guide in running reports on either customer by product or product by customer.

Figure 11.7

By including two columns in the Output range on a Unique Values query,we get every combination of Customer and Product.

0 0

Post a comment