• ARTICLES
• SUBSCRIBE
SEARCH

## (Solved) - Identify identical data by column and row

(4 posts)
• Started 9 months ago by WHITEHOUSEP
• Topic Viewed 331 times

• Remove Solved Status
WHITEHOUSEP
Posts: 12

I have a large spreadsheet which is just 3 columns wide, each cell contains a postcode.

AB42 3JF AB42 3JF AB42 3JF
AB42 4HR EH17 7NQ EH17 7NQ
AB51 5EU G2 6HS G2 6HS

I am looking for a formula to identify any row in which the 3 postcodes are identical. For example, I would like the first row shown above flagged.

I would appreciate any suggestions!

Charlotte

Posted 9 months ago #

moreeg
Posts: 842

Hi Charlotte

you can try this formula in Col D assuming your post code are in A, B and C

=IF(A1="","",IF(AND(A1=B1,B1=C1),"Flagged",""))

replace "Flagged" with however you want to flag them.

Alternatively, you can set up a Conditional Format using the same equation to highlight the target rows with a colour.

the conditional format rule would be

=AND(\$A1<>"",AND(\$A1=\$B1,\$B1=\$C1))

Posted 9 months ago #

moreeg
Posts: 842

EDIT: (missed the cutoff)

If you go for the first option a more efficient version of the equation is

=IF(AND(A1<>"",A1=B1,B1=C1),"Flagged","")

same for the conditional rule option

=AND(\$A1<>"",\$A1=\$B1,\$B1=\$C1)

Accepted Answer · Posted 9 months ago #

WHITEHOUSEP
Posts: 12

That's it! Thanks so much, I've sorted the whole spreadsheet in a minute. :)

Posted 9 months ago #

## Topic Closed

This topic has been closed to new replies. Please create a new topic instead.