Create a unique distinct list using Advanced Filter in a macro [VBA]
Question:
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
Press with left mouse button oning 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
- Press with left mouse button on "Developer" tab How to show the Developer tab or run in developer mode
- Press with left mouse button on "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
' Run 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
Get excel sample file for this tutorial.
Remember to enable macros.
quick unique distinct list.xls
(Excel 97-2003 Workbook *.xls)
Advanced filter excel category
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Question: How do I filter the last xx years or xx months in Excel? How do I exclude the current […]
This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Excel categories
15 Responses to “Create a unique distinct list using Advanced Filter in a macro [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
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
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
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.
David,
To remove "flipping" use: Application.ScreenUpdating = False
/Oscar
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
david,
Advanced Filter uses your first value as a column header.
/Oscar
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!!!
joško,
See this post: https://www.get-digital-help.com/2010/07/17/create-dependent-drop-down-lists-containing-unique-distinct-values-in-excel/
Hi, Oscar!
"Maybe someone else have some lightning fast formula technique?"
You can try and explore the following, something different approach:
I tried this on my laptop with Excel 2007:
The following macro fills the column A with 1048574 random numbers between 1 and 1048574
Public Sub fillrandomnumbers()
Dim m As Long
m = 1048574
With Range("a1:a" & m)
.Formula = "=RandBetween(1,1048574)"
.Value = .Value
End With
End Sub
Now, it is needed to sort the list in ascending order!
Then, to get a unique list of numbers into column B run the following macro:
Sub uniquelist()
Dim s As Long
Dim v As Variant
Dim z As Long
s = 1048574
v = [a1]
Columns("A").Insert
With Range("a1:a" & s)
.Formula = "=if(b1<>b2,b2)"
.Value = .Value
.Sort Range("a1"), xlAscending
z = .SpecialCells(2, 3).Count
Range("c1:c" & z) = .Value
End With
Columns("A").Delete
[b1] = v
End Sub
It takes about 20-25 second to complete this huge task on my laptop.
Remarks:
The last macro works with strings as well. The sorting procedure can also be inside the macro. And there must be at least one free cell below the initial list.
Hi..would u provide some video tutorial for this?
It seems some characters may be lost in the macro code after posting.
If running macros fails then you can send me a request and i will send a file by email:([email protected])
Thanks Eero for your contribution!!
This is giving me an error when I try to use it with numbers. I am following your syntax exactly.
Excel 2010.
Am I doing something wrong?
William,
the attached file works here (excel 2010).
What is the error message? You can upload the file here if you want me to look at it.
Oscar, this is good stuff. Wondering if you could help me take this a step further. I have a named range called "Date" in column C which is dynamically pulling from another data source with a few other columns of data. I would like to use VBA to create a unique list in another dynamic column range called "Date2".
If I may, additionally I would like to do a sumif function in Table2 (which contains Date2 in one column and Cost2 in the column next to it) in Cost2 all of the costs in column Cost (pulled in from the aformentioned query) if the date in Date2 matches Date. I was hoping to keep all of this dynamic so I can have a chart which shows cost over time automatically when I press with left mouse button on the button. Can this be done?
|Date | Cost | Date2 | Cost 2|
7/26/2015 $5
7/26/2015 $6
8/1/2015 $7
9/1/2015 $8