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).
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)?
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)
' Extending the selection down to the cell just above the first blank cell in this column
' Execute Advanced Filter on selection and copy to Sheet1!C2
Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
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)