In the Excel user interface, to extract a unique list of products that were purchased by customer ABC, select Advanced Filter and set up the Advanced Filter dialog as shown earlier in Figure 11.8. The results are shown in Figure 11.9.

In VBA, you would use the following code to perform an equivalent advanced filter.

Sub UniqueProductsOneCustomer() Dim IRange As Range Dim ORange As Range Dim CRange As Range

1 Find the size of today's dataset FinalRow = Cells(65536, 1).End(xlUp).Row NextCol = Cells(1, 255).End(xlToLeft).Column + 2

' Set up the Output Range with one customer

Cells(1, NextCol).Value = Range("D1").Value

' In reality, this value should be passed from the userform

' Set up output range. Copy heading from B1 there Range("B1").Copy Destination:=Cells(1, NextCol + 2) Set ORange = Cells(1, NextCol + 2)

' 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, _

CriteriaRange:=CRange, CopyToRange:=ORange, Unique:=True ' The above could also be written as:

'IRange.AdvancedFilter xlFilterCopy, CRange, ORange, True

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

' Sort the data

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

0 0

Post a comment