Compare data in multiple columns to find duplicates & conditionally highlight

[ndube50]

I am attempting to use conditional highlighting to highlight duplicate values in a range of data in excel 2007.

Here is exactly what I want:
Column A is a function of a 'toggle switch'. So if I change conditions of the toggle switch the values in column A change.

Columns B through N have static values in them. Some columns have duplicate values within them (i.e. column B has two entries for 'iphone')

I want cells within each column (B:N) to highlight when they match a value in column A.

Example:
Column A
iphone
Blackberry
Nokia
Column B
motorola
Ericsson
Samsung
Column C
LG
iPhone
Blackberry
Sanyo

Ideally, this formula would be backwards compatible with excel 2003.

I'm not familiar with VB so I need it to be a function.

Thanks in advance!!

 

[GlennUK]

Surely that's a single simple condition. Select B1:N999 ( or however many rows of data there are ), and create Conditional Formatting with a formula condition of:
=B1=$A1
and choose a format.

 

[ndube50]

http://www.divshare.com/download/15207354-c03

Hi,
I uploaded an example file to show you what I am looking to do.
In the worksheet called 'stage summary', i have it set up as follows:

Range A6:A150: values are a function of the cell A2 which can be toggled to one of several different options. (example: select 'unclassified' from the drop-down menu in A2 and you will have only 1 value in range A6:A150. If you select all, you'll have 132 entries).
Array B6:M150 are autopopulated cells taken from the worksheet 'Targets'. The cells do not change in response to changes in cell A2.

What I want, is for cells in the array B6:M150 to highlight red when they are duplicates of values in the range A6:A150.

PROBLEM: Some values appear more than once in array B6:M150 (example: Exa77 appears across several different stages and twice within one stage). I don't want these values highlighted unless they match a value in column A.

Glenn's formula did not appear to work for me. I appreciate the suggestion, but the wrong cells were highlighting using =B1=$A1.

 

[GlennUK]

I cannot navigate that link, due to security policies here where I work.

Can you post a section on here using Excel Jeanie, so that I see what you mean when you refer to "duplicates"?

http://www.excel-jeanie-html.de/index.php?f=1

 

[ndube50]

I cannot navigate that link, due to security policies here where I work.

Can you post a section on here using Excel Jeanie, so that I see what you mean when you refer to "duplicates"?

http://www.excel-jeanie-html.de/index.php?f=1

Here is the link to the other posting at Mr Excel. Thanks.

http://www.mrexcel.com/forum/showthread.php?p=2776520#post2776520

 

[ndube50]

I cannot navigate that link, due to security policies here where I work.

Can you post a section on here using Excel Jeanie, so that I see what you mean when you refer to "duplicates"?

http://www.excel-jeanie-html.de/index.php?f=1

Whoops, meant to send you think link Glenn at Excel Forum:
http://www.excelforum.com/excel-general/782787-compare-data-in-multiple-columns-to-find-duplicates-and-conditionally-highlight.html#post2557003

I was able to upload the file directly there.

 

[ndube50]

Whoops, meant to send you think link Glenn at Excel Forum:
http://www.excelforum.com/excel-general/782787-compare-data-in-multiple-columns-to-find-duplicates-and-conditionally-highlight.html#post2557003

I was able to upload the file directly there.

Hi Glenn,

I was able to solve this problem with this function:
=ISNUMBER(MATCH(B6,$A:$A, 0))

I do have another issue I am working on if you wanted to take a stab at it.
The workbook has been uploaded to Excel Forum as well here:
http://www.excelforum.com/excel-gen...range-in-a-patterned-formula.html#post2557384