Using frequency in a data validation formula to ensure unique entries

[Rkeev]

Using frequency in a data validation formula for ensuring unique entries.


Currently I use COUNTIF($B$2:$B$244,B2)<=1 or ISNA(VLOOKUP(A9,A$1:A8,1,FALSE)) to ensure all entries in particular columns are unique, but I have heard how slow these functions are and that the Frequency function counts the fastest for larger spreadsheets. My spreadsheet has over 40K lines so I don’t think these other formulas will be fast enough.


I also know frequency only counts numbers, so my question: is there a way to use frequency with text items in a data validation to ensure unique entries? All items are 5 digits with 1 letter in them.


Thx,
RKeev

 

[Stridhan]

for example, if your range would have been a1:a4 then

=OMFEL(INDEX($A$1:$A$4;MINSTA(OM(FREKVENS(PASSA($A$1:$A$4;$A$1:$A$4;0);PASSA($A$1:$A$4;$A$1:$A$4;0))>0;RAD($A$1:$A$4)-RAD($A$1)+1;"");RADER($B$1:B1)));"")

 

[Stridhan]

for example, if your range would have been a1:a4 then

=OMFEL(INDEX($A$1:$A$4;MINSTA(OM(FREKVENS(PASSA($A$1:$A$4;$A$1:$A$4;0);PASSA($A$1:$A$4;$A$1:$A$4;0))>0;RAD($A$1:$A$4)-RAD($A$1)+1;"");RADER($B$1:B1)));"")

woops sorry thats in swedish

ctrl+shift+enter

=iferror(INDEX($A$1:$A$4,small(if(frequency(match($A$1:$A$4,$A$1:$A$4,0),match($A$1:$A$4,$A$1:$A$4,0))>0,row($A$1:$A$4)-row($A$1)+1,""),rows($B$1:B1))),"")

 

[Rkeev]

for example, if your range would have been a1:a4 then

=OMFEL(INDEX($A$1:$A$4;MINSTA(OM(FREKVENS(PASSA($A$1:$A$4;$A$1:$A$4;0);PASSA($A$1:$A$4;$A$1:$A$4;0))>0;RAD($A$1:$A$4)-RAD($A$1)+1;"");RADER($B$1:B1)));"")

Stridan,
thanks for the quick response.

Not sure if the small-if-frequency-match function will be faster than the countif, but I'll give it a try.

Rkeev

 

[Stridhan]

i think i got i wrong, my formula creates a unique validationlist, it aint checking if its unique