Pittsburgh Business Daily

How do I create lists of unique values for multiple columns in Excel for large datasets?

I have multiple columns, and I need to extract unique values for each column separately. Pivot table seems to be the fastest for large datasets, but I have to do one column at a time. Macro code does it for me, but it is very slow. Advanced filter also does it one column at a time, and is more cumbersome than pivot table. If I can use the unique fields function from pivot table into macro, it would be a lot faster. Any ideas?

Public Comments

1. Say your data is in A1:A8 Put this in A10 (Leave A9 blank) =INDEX ($A$1:$A$8,MATCH (0,COUNTIF (A$9:A9,$A$1:$A$8),0)) This is an array formula. So when you enter the formula, press Ctrl+Shift+Enter instead of just Enter. When there are no more unique values to return, the formula will return #N/A. To get rid of those, use IFERROR function if you use 2007. Any other version, use ISERROR & IF. Add details or email me if you need more info.