Author: Oscar Cronquist Article last updated on December 17, 2018

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

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

  1. Press with left mouse button on "Developer" tab How to show the Developer tab or run in developer mode
  2. Press with left mouse button on "Visual Basic"
  3. Create a "Module" for your workbook How to Copy Excel VBA Code to a Regular Module
  4. 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)