SEARCH

The How-To Geek Forums Have Migrated to Discourse



Join the Discussion at discuss.howtogeek.com

How-To Geek Forums / Windows XP

(Solved) - Identify identical data by column and row

(4 posts)
  • Started 2 months ago by WHITEHOUSEP
  • Latest reply from WHITEHOUSEP
  • Topic Viewed 170 times

  • View Accepted Answer
  • 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 2 months ago #
Top
 
moreeg
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 2 months ago #
Top
 
moreeg
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 2 months ago #
Top
 
WHITEHOUSEP
Posts: 12

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

Posted 2 months ago #
Top