Create a unique distinct list of a long list without sacrificing performance using vba in excel

Filed in Excel on Feb.10, 2010.

Question:

hi all,

thanks for the great formula/array formula. it works great.

lately, i noticed that the array formula will make the excel calculations CRAWL if I have thousands of entries (dates; but mostly repeated because of different product).

e.g.

1-Jan-2010 ProductA
1-Jan-2010 ProductB
1-Jan-2010 ProductC
1-Jan-2010 ProductD
2-Jan-2010 ProductA
2-Jan-2010 ProductB
2-Jan-2010 ProductC
...
...

the unique distinct values from the dates are 1-Jan-2010, 2-Jan-2010, and so on.

In a month (31-days) x 13 products = 403 entries. Multiply this to 12-months = 403 x 12 months ~est. 4.9k dates to be parsed.

the jackpot question: Is there a way to easily parse long list, without sacrificing performance (recalculations)?

thx!

A comment from this blog post: How to extract a unique distinct list of a column in excel

Answer:

I created a random list of 5000 values. Creating a unique distinct list using Advanced Filter only took a couple of seconds. Maybe that is fast enough?

I also tried sorting the list and using MATCH() (which is fast) to filter unique distinct values but that required a lot of manual work. Maybe someone else have some lightning fast formula technique?

I then created a macro, recording my actions using Advanced Filter. I edited the vba code and inserted a button (Form Control).

How to Run an Excel Macro With a Worksheet Button

Clicking the button refreshes the unique distinct list. Adding values to the list is no problem, the selection extends as long as there are no blank cells.

How to automatically use Advanced Filter to create a unique distinct list

Click "Developer" tab How to show the Developer tab or run in developer mode
Click "Visual Basic"
Create a "Module" for your workbook How to Copy Excel VBA Code to a Regular Module
Copy this vba code into module:

Sub AdvFilter()
'
' AdvFilter Macro

' Select first cell in column (Sheet1!A2)

Range("Sheet1!A2").Select

' Extending the selection down to the cell just above the first blank cell in this column

Range(Selection, Selection.End(xlDown)).Select

' Execute Advanced Filter on selection and copy to Sheet1!C2

Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Sheet1!C2"), Unique:=True

End Sub

Assign macro to button

How to Run an Excel Macro With a Worksheet Button

Download excel sample file for this tutorial.

Remember to enable macros.

quick unique distinct list.xls
(Excel 97-2003 Workbook *.xls)

Share and Enjoy:

Comments (8)

8 Responses to “Create a unique distinct list of a long list without sacrificing performance using vba in excel”

david Says:
February 11th, 2010 at 2:23 am
hi oscar, finally been trying very hard to use this. my situation as such:

1) First sheet is called VC (the master table with dates).
2) Second sheet is called Calc (where the calculations are located).
3) Third sheet is called Charts (where the VBA-macro button is located).

Charts sheet is the only visible sheet. Managers are viewing this only.
Calc sheet is hidden (to prevent ppl snooping or ruin the formulas )

It seems that Advanced Filter function itself cannot PUSH filtered data to Calc sheet (target).

I've tried the other way round to PULL filtered data from VC sheet (source). However, the macro uses an absolute address of the selected range.

this is the vb code in Calc sheet:
Sub Macro3()
'
' Macro3 Macro
'

'
Sheets("VC").Range("A2:A5004").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("C2"), Unique:=True
End Sub
Oscar Says:
February 11th, 2010 at 10:05 pm
David, try this:

Sub AdvFilter()
'
' AdvFilter Macro

Range("VC!A2").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Calc!C2"), Unique:=True

End Sub
david Says:
February 12th, 2010 at 12:33 am
thx oscar. it works, but only after i added this:

Sub AdvFilter()
'
' AdvFilter Macro
Sheets("VC").Select
Range("VC!A2").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"Calc!C2"), Unique:=True

Sheets("Chart").Select

End Sub

-----------

problem with Adv Filter is that it cant just pull data from another sheet. The sheet must be active before Adv Filter can be executed.

my rough tweak is to Select the VC sheet first, then let the Adv Filter routine run (generated on Calc sheet). Then change back to Chart sheet where the button is.

this creates a short sheet "flipping" when the button is created.
Oscar Says:
February 17th, 2010 at 10:22 pm
David,

To remove "flipping" use: Application.ScreenUpdating = False

/Oscar
david Says:
February 19th, 2010 at 3:04 am
thx oscar, the "no-flipping" function works.

btw, do u know that the Adv Filter-unique value doesnt work if the first 2 values are the same?

e.g.

10001
10001
10001
10002
10002
10003
10004
10004
10005

generated list from Adv Filter are...

10001 <-repeat
10001 <-repeat
10002
10003
10004
10005
Oscar Says:
February 19th, 2010 at 9:34 am
david,

Advanced Filter uses your first value as a column header.

/Oscar
joško Says:
July 2nd, 2010 at 8:46 pm
What would the code look like if I have two collumns?

In first one are names of people and they can occur many times.
In the second one there are different cities. So for each name one or more different cities can occur in second column.

The upper code does well when listing unique names. What if in certain cell I select from dropdown list one of these names and excel has to create secondary unique list (based on only one choosen name from column one). Any suggestions? Would mean a lot!!!
Oscar Says:
July 17th, 2010 at 11:08 pm
joško,

See this post: http://www.get-digital-help.com/2010/07/17/create-dependent-drop-down-lists-containing-unique-distinct-values-in-excel/