CODEWITHSUNDEEP
excelexcel-formulacountif
Tom Sutherland
Tom Sutherland

Reputation: 53

Excel COUNTIF return FIRST unique value

I am designing a spreadsheet which will use formulas to extract unique data from a column whilst also ignoring empty cells.

I am using the following formula to mark data as whether it should be used, or is a duplicate

=IF(B6<>"", IF(COUNTIF(B7:B$35,B6),"No","Yes"),"")

This works, but marks the last occurrence of duplicate data as the one to use. I need to use the first occurrence to preserve my preferred order.

E.g. the current formula will extract ABDC from A, B, C, D, C, because the last C occurs after the D. I need it to take the FIRST C to read ABCD.

Upvotes: 0

Views: 476

Answers (1)

Chronocidal
Chronocidal

Reputation: 8081

Flip your locked range around, and check for "does this already exist above" instead of checking below.

To do this, change IF(COUNTIF(B7:B$36,B6),"No","Yes"), to IF(COUNTIF(B$1:B5,B6),"No","Yes") or IF(COUNTIF(B$1:B6,B6)>1,"No","Yes")

Upvotes: 1

Related Questions