Retrieve Uniques on 11000 Entries

[somebody113]

Hi,

I have two codes that will retrieve unique records, though, when I run it on unique records, it crashes excel, I want to avoid VBA code if possible. Here are the two codes that do work, but are too cumbersome, Any leaner ideas?

Code 1

=INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0))

Code 2

=IF(ISNA(OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$13)=B$1:B1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)),"",OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$13)=B$1:B1),ROW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1))

 

[Michael M]

From memory there is a filter limit for unique records in Excel 2000 /03of 1000 unique entries.
Which suggests you may have to look at either rearranging your data into smaller lots OR you will have to embrace a VBA solution

 

[martindwilson]

advanced filter unique will work on over 1000 unique values, its only the drop down that is limited to view the first 1000

 

[Michael M]

Thanks for the pickup Martin....

 

[brettdj]

My dupe master addin, http://www.experts-exchange.com/A_2123.html, will list the uniques, as an addin you may find it less cumbersone that VBA

The advantages of the addin over the formula approach are

- the addin allows you to specify true uniques (that only occur once) or uniques that occur at least once
- functionality to ignore case sensitivity
- functionality to ignore white spaces
- regular expression replace functionality

Cheers

Dave