Compare 2 Columns/sheets with simialr data then either colour or display duplicates?

Hi There,

I am after something pretty simple I guess, but feel completely useless with Excel! I have searched on this forum and all answers seems more complex than I need or am able to re-engineer!

I have 2 sets/lists of computer names - one is a large list (A) and one is a subset of the larger list (B). I can have these in a separate worksheet or for now in Column A and Column B.

I want to either colour (column B) or display in Column C if values in Column B appear anywhere in Colum A - they are not going to be lined up or in an order, so would be anywhere in the columns/array.

Example (below there is a single match for example)

........A...............B...........C
1...PC1kfg......PC1hak.........
2...PC123f......PC12345......
3...PC5wrp0....PC1kfg........
4...PC13jms......................

...etc

Is there an easy way to do this? Is I vlookup?

Thank You Very Much

Hi cluele55

To colour a cell value in column B if it is in column A, use conditional formatting, select column B and for the rule: =MATCH($B1,A:A,0), format as required.

Or in C2 and copy down
=IF(COUNTIF(A:A,B2)>0,"Match","")

what formula are you putting in column C!

Hi cluele55

See the attached the colour in column B is conditional formatting using : =MATCH($B1,A:A,0)

On the home tab, conditional formatting, new rule.

Hi cluele55

To sum as per your sheet in post #5
=SUMPRODUCT(COUNTIF(A1:A5,B1:B4))

No problem cluele55, Glad your sorted and thanks for the back.