CODEWITHSUNDEEP
google-sheetsgs-conditional-formattinggoogle-sheets-formula
azizLIGHT
azizLIGHT

Reputation: 183

Highlight cell if value is present in any cell in another column

I want to highlight the cell in column A if it is repeated anywhere in column B. For example:

A      | B
pack_1 | unrelated
pack_2 | unrelated
pack_3 | pack_1
pack_4 | pack_1
pack_5 | pack_3
pack_6 | pack_3
pack_7 | unrelated
pack_8 | pack_2

In the example, pack_1, pack_2 and pack_3 should be highlighted because they are mentioned in column B.

How can I do such a thing?

Upvotes: 18

Views: 64324

Answers (4)

user17130918
user17130918

Reputation: 21

You would add conditional formatting to A1 and then copy to rest of the cells in the column. Set the conditional formatting to custom formula and make the formula:

=COUNTIF($B$1:$B$8,A1)

And set your color. It will highlight the cells in the A column if they exist in the B column. (Keep in mind I Am assuming your range is B1:B8)

Upvotes: 2

furman87
furman87

Reputation: 968

Do conditional formatting on each cell with the formula:

=EQ(VLOOKUP(A1, B:B, 1, FALSE), A1)

And format the cell to a different color if it matches.

I shared an example here (this link will ask you to make a copy in your own Google Drive account):

https://docs.google.com/spreadsheets/d/1IovLko1cF2guKnIalCyE0uSbCvMDYLgL0BZHt35znXI/copy

Upvotes: 19

KERR
KERR

Reputation: 1692

  1. Select both columns
  2. click Conditional Formatting
  3. click Highlight Cells Rules
  4. click Duplicate Values (the defaults should be OK)
  5. Duplicates are now highlighted in red:

Example. Duplicates are highlighted in red

Upvotes: -1

pnuts
pnuts

Reputation: 59442

Please select ColumnA, Format, Conditional formatting..., Custom formula is:

=match(A1,B:B,0)>0

with formatting of choice.

Upvotes: 17

Related Questions