kuldeepjainesl
Member
Hi Experts,
I need to get the list of unique distinct list from the coloum where it is expected that coloum will have more than 500K values....while unique distinct list may have only 2K-3K values.
I tried the below mentioned solution and it work fine for smaller ranges (say 100-1K) but as soon as it get a range of more than 100K, this is not efficient.
Formula which I used =INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0)) with (Ctrl+Shift+Enter)
May I have a UDF/Macro/Formula to overcome this? If I use remove duplicate of excel than it do the job in seconds...but it will become manual activity which is not desired.
You are requested to use dynamic range in formula so that it take the minimum time as currently I have around 100,000 value listed in coloum but expected to raise.
Regards,
Kuldeep
I need to get the list of unique distinct list from the coloum where it is expected that coloum will have more than 500K values....while unique distinct list may have only 2K-3K values.
I tried the below mentioned solution and it work fine for smaller ranges (say 100-1K) but as soon as it get a range of more than 100K, this is not efficient.
Formula which I used =INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0)) with (Ctrl+Shift+Enter)
May I have a UDF/Macro/Formula to overcome this? If I use remove duplicate of excel than it do the job in seconds...but it will become manual activity which is not desired.
You are requested to use dynamic range in formula so that it take the minimum time as currently I have around 100,000 value listed in coloum but expected to raise.
Regards,
Kuldeep