Efficient formula to extract unique values from large list (> 10k records)

[rubthebuddha]

Does anyone have a formula for extracting unique values from a single column of a large (>10k records) list that doesn't bog down Excel?

Formulas are desired, as replacing duplicates and using macros/custom functions are beyond most of my end users.

The formula I usually use is similar to this:

=IFERROR(INDEX($B$2:$B$100,MATCH(0,COUNTIF($A$1:$A1,$B$2:$B$100),0)),"")

in which the data set is in B and the values are returned in A. It works for a few hundred rows just fine, but when I get in the thousands, Excel gets bogged down and becomes almost non-functional.

 

[RussellScott]

Forgive me, I didn't know the formula in D had to be entered as an array formula. After doing this it now works! and FAST! Big performance increase from what I can tell!

A few clarifications that might be helpful to anyone struggling with this like me:

1) The named ranges can have '1'! and '6'! omitted.
2) Formula in D also has to be entered as an array formula
3) No idea what the J column reference is all about but just go with it
4) No idea what the 'range' named range is for but again, just go with it

Hope this helps someone, and thank you so much for a much improved formula!

 

[Aladin Akyurek]

Forgive me, I didn't know the formula in D had to be entered as an array formula. After doing this it now works! and FAST! Big performance increase from what I can tell!

A few clarifications that might be helpful to anyone struggling with this like me:

1) The named ranges can have '1'! and '6'! omitted.
2) Formula in D also has to be entered as an array formula
3) No idea what the J column reference is all about but just go with it
4) No idea what the 'range' named range is for but again, just go with it

Hope this helps someone, and thank you so much for a much improved formula!

What did you opt for? Post #5 suggests an effective, flexible formula system for generating a unique/distinct list. The performance depends on the number of conditions and the sizes of the ranges involved. If it's not fast enough, other set ups are possible (but not one with COUNTIF(S), and one with MIN for that matter).

 

[RussellScott]

What did you opt for? Post #5 suggests an effective, flexible formula system for generating a unique/distinct list. The performance depends on the number of conditions and the sizes of the ranges involved. If it's not fast enough, other set ups are possible (but not one with COUNTIF(S), and one with MIN for that matter).

Hello thanks for getting back to me. In the end I did go for Post #5 with the modifications/workarounds I noted. My posts were just initial confusion, and then I endeavoured to clarify the instructions that I found a little unclear. It's certainly a great way of doing it, and it has worked for my requirements.

 

[Aladin Akyurek]

Hello thanks for getting back to me. In the end I did go for Post #5 with the modifications/workarounds I noted. My posts were just initial confusion, and then I endeavoured to clarify the instructions that I found a little unclear. It's certainly a great way of doing it, and it has worked for my requirements.

Great. Thanks for the feedback.