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?
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
Please select ColumnA, Format, Conditional formatting..., Custom formula is:
=match(A1,B:B,0)>0
with formatting of choice.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With