Comparing Duplicates From 2 Lists

[mason001]

hi all,

So basically put, I have 2 lists, one containing a list of areas nationally and a list of areas within my patch. I want to highlight cells within the national list if it appears within the my area list.

I would obviously usually use conditional formatting and highlight duplicates, however in this instance I can’t as the areas within thenational list appear several times each; so highlighting duplicates highlightseverything!

A very short example of what I’m after being:

National list: Myarea list:
Column A: ColumnB: ColumnC:
Brighton 1 Crawley
Brighton 2 Newcastle
Brighton 3 Birmingham
Brighton 4
Crawley 1

Crawley 2

Dartford 1
Dartford 2
Dartford 3

Manchester 1

Newcastle 1

Newcastle 2



Any help with this would be much appreciated as I’ve beenwracking my brain and googling but can’t find the answer! I'm using Excel 2013.

Thanks!

 

[mason001]

ah... so the layout of my example didn't work in my initial post.

Column A is national area name, B is number set against the national area, C is my area list.

 

[Marcelo Branco]

Select the data in column A (A2:A18)

In CF use this formula
=ISNUMBER(MATCH(A2,C$2:C$4,0))

Format button and pick the format you want

M.

 

[mason001]

Select the data in column A (A2:A18)

In CF use this formula
=ISNUMBER(MATCH(A2,C$2:C$4,0))

Format button and pick the format you want

M.

you legend/wizard! that's worked perfectly! thank you so much.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.