Limitation on formula to create list of unique items

[cman0]

This may be an Excel 2010 issue only.

There is a limit to the usability of the

=INDEX($A$1:$A$1000,MATCH(0,COUNTIF($A$1:$A$1000,"<"&$A$1:$A$1000),0))

method.

It seems like the limitation is in the Countif function going over 1000 (or some other size limit)

I have a list of ~1500 rows, of that there are approximately 55 unique items. Doing the unique array works correctly up until item ~40, upon which it fails by returning the 1st item only (for the rest).
Trying to debug, and pulling out the Match section, it functions up until 976 (that is

MATCH(0,COUNTIF($N$1:$N40,$C$3:$C$1500),0)

returns 976) anything after returns just 1 (1st item).

My Googlefu is failing, but it seems to be a limitation on the text string size that COUNTIF can handle.

Does anyone know of a fix, or some other reason why this will stop working when it works perfectly on the rest of the sheet?

[JBeaucaire]

Post your sample workbook with your formulas in them and we'll take a look together at the same data. You may be right.

[cman0]

Excel Sample workbook3.xlsx

So i fixed the problem. Somehow my file was corrupted, in my attempts to streamlining it for posting it got fixed. No idea how mind you, but there it is. I've attached the working copy as an example if anyone is interested. The original problem file is over 5MB which should have been an indication of an underlying problem.

Info on the attached file,
- Columns B - H, raw original data
- Column N, unique names of Column C
- Column T & U are testing only

What i was trying to do, is populate Column D-H automatically, instead of manually entering all 1500 lines. Enter it once per equipment, and when completed simply copy/paste 'values only'.

Anyway, thanks for getting me to streamline the document and fix it. It looks like Match() does work past 1000 (as per Column U).

If anyone wants to find out where the problem originated from, i still have the 5+MB file with the issue.

[AlKey]

You might want to try this array formula. It will extract unique values and ignore blanks.


=IFERROR(INDEX($C$3:$C$1369,MATCH(0,IF(ISBLANK($C$3:$C$1369),"",COUNTIF(N$1:$N1, $C$3:$C$1369)), 0)),"")

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[cman0]

You might want to try this array formula. It will extract unique values and ignore blanks.


=IFERROR(INDEX($C$3:$C$1369,MATCH(0,IF(ISBLANK($C$3:$C$1369),"",COUNTIF(N$1:$N1, $C$3:$C$1369)), 0)),"")

Nice additional step, cleans up the listing. Thanks

[AlKey]

You're welcome. Don't forget to thank those who helped by clicking on Add Reputation *

[AlKey]

Thank you for your feedback!