Flag Unique Values with Multiple Criteria in large Dataset

[skumar14]

Hello I'm trying to flag unique values in an extremely large dataset with about 498,000 rows however every formula combination i've used (sumproduct and countif functions) always lock up my excel sheet. Does anyone know if there is a simpler way that doesn't use a large amount of system resources to flag unique values? Any help would be greatly appreciated. Thank you!

 

[Aladin Akyurek]

A1 houses a header.

Maybe, B2, copied down:

=1-ISNUMBER(MATCH(A2,$A$1:A1,0))

Unique is marked as 1, non-unique as 0.

 

[skumar14]

Thanks for the quick reply! I tried this and it still is locking up my excel, and I don't want to break up the dataset into multiple sheets because it would be too complicated. Is there possibly another way to go about this?

 

[Aladin Akyurek]

Thanks for the quick reply! I tried this and it still is locking up my excel, and I don't want to break up the dataset into multiple sheets because it would be too complicated. Is there possibly another way to go about this?

Can you sort the data in ascending order?

 

[skumar14]

Yeah I've sorted the data in ascending order and closed out all programs besides excel and it is still locking out.

 

[Aladin Akyurek]

Yeah I've sorted the data in ascending order and closed out all programs besides excel and it is still locking out.

Very possible with the amount of data you have:

Does this...

=1-(A2=A1)

also lock up with the sorted data?

 

[skumar14]

This formula worked, excel didn't lock out this time! However I maybe misunderstanding the logic and this might be a stupid question, but how is this flagging the unique values? Also thank you for all your help with this issue!

 

[Aladin Akyurek]

Very possible with the amount of data you have:

Does this...

=1-(A2=A1)

also lock up with the sorted data?

This formula worked, excel didn't lock out this time! However I maybe misunderstanding the logic and this might be a stupid question, but how is this flagging the unique values? Also thank you for all your help with this issue!

#Sorted List#
FAD
FAD
LAD
LAD
LAD
VAD

in B2:B7 would yield...

=1-(A2=A1) ==> 1-(FALSE) ==> 1-0 ==> 1
=1-(A3=A2) ==> 1-(TRUE) ==> 0
=1-(A4=A3) ==> 1-(FALSE) ==> 1

And so on, where 1 is the unique marker. Note that TRUE is 1 in Excel and FALSE 0.