Excel COUNTIF return FIRST unique value

Question

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.

Chronocidal · Accepted Answer · 2019-06-06 09:55:16Z

1

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")

answered Jun 6, 2019 at 9:55

Chronocidal

8,4201 gold badge15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Sutherland Over a year ago

Perfect, thanks so much - that's done the job. I tried reversing the range before but it didn't seem to do anything. I must have made a mistake, your formula works great :-)

Collectives™ on Stack Overflow

Excel COUNTIF return FIRST unique value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related