Unique values in Table (most efficient way)

[Freedox]

Hi,

I'm using a formula below to determine if the formula is unique. If the ID is unique, then write 1, if repeat then 0. All values are in a table with 100k+ lines and the formula seems to extremely resource intense...

Does anyone know if there is a more efficient way of calculating unique values?

=IF(COUNTIF($X$2:X2,[@[WEB_ID]])>1,0,COUNTIF($X$2:X2,[@[WEB_ID]]))

 

[FormR]

Hi, is column X the Web_ID column?

This would be a little faster:

=0+(COUNTIF($X$2:X2,[@[WEB_ID]])=1)

And if you can sort your data on column X then this would be very fast.

=1-(X2=X1)

 

[Freedox]

Thanks, FormR, it looks like it's faster, nowhere near I wish it was... but slight improvement Cheers!

 

[FormR]

it's faster, nowhere near I wish it was

Hi, are you not able to sort on that column?

 

[FormR]

are you not able to sort on that column?

Assuming not - is this any better?

=0+(MATCH(X2,X$2:X2,0)=ROWS(X$2:X2))

 

[DaleW]

=MATCH(x2,x:x,0)-ROW()

Where ever there is a zero result is the first occurrence. Sort column where you place this formula (largest to smallest) to place all zeros at the top and those will be your unique values.

 

[DaleW]

Sorry, left something out. Enter the formula =MATCH(x1,x:x,0)-ROW() in each row of containing data in a blank column

 

[Freedox]

Hi, are you not able to sort on that column?

I'am, but info comes in from SQL server and every time I'm updating it I thought it would mess it up all data... However, ran a few tests and it looks like it's working! And you are right it's million times faster!

Thanks for that!!

 

[FormR]

Great, good to hear

info comes in from SQL server

You could even change your query / SQL statement to ensure it's sorted on the column if it's not already.

 

[Freedox]

Great, good to hear



You could even change your query / SQL statement to ensure it's sorted on the column if it's not already.

Genius!!!! Cheers man! we have a winner here :D