Highliting Duplicate Cells across multiple rows
 

Hi,

I recently started a major project for the company I'm working for, and
I've been struggling all morning trying to figure out how to get where
I'm going next.

Currently, I have all my columns, about twenty of them, in one
worksheet. They are side by side. It displays the part numbers that
different companies produce, and each column is for a different
company. I need some way to highlight the cells in each column that the
numbers are unique to that column alone, and aren't in any other column.


I also need a way to do a simple search and be able to sort and see
which companies produce a single part number. The columns have
different amounts of rows in them...some of them have over two thousand
part numbers, while some only have 30 or 40. How do I get excel to
compare all twenty of the lists to each other and highlight the part
numbers that only that company makes?


Thanks so much in advance....I've been working on this for about four
hours now and it's starting to make me mad.


I don't know if I have to put a macro in, or conditional formatting, or
what. I've done a lot of research and trial and error on it, and I can't
seem to find anything that will compare multiple ranges, and I really
don't know enough about excel to really get in and create my own
formula.

Any help would be greatly appreciated.....but I need detailed stuff,
like step-by-step in order to be able to do it right. Thanks!

-Sally


--
equinekingdom
------------------------------------------------------------------------
equinekingdom's Profile: http://www.excelforum.com/member.php...o&userid=33457
View this thread: http://www.excelforum.com/showthread...hreadid=532633

Highliting Duplicate Cells across multiple rows
 

Hi Sally,

Try this to highlight all non-blank cells which have a unique entry on
the worksheet:

Go to cell A1. Press Ctrl-A to highlight the worksheet.
Select Format-Conditional Formatting
Select "Formula Is" and enter:
=AND(A1<"",COUNTIF($1:$65536,A1)=1)
Using the format button, select the pattern you wish to show for unique
items
Select OK

Of course, it could just as easily be non-unique entries you
highlight.

Hope this works for you. I'll be interested to read how it goes.

Cheers,


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532633

Highliting Duplicate Cells across multiple rows
 

OK,

Now to get a bit more specific to your query. Following the same
steps, replace the above formula with:
=AND(A1<"",COUNTIF($1:$65536,A1)-COUNTIF(A:A,A1)<0)

This should hopefully highlight entries in a column which are also in
another column, indicating that more than one company produces the
part. The ones that aren't highlighted therefore are only produced by
one company.

Could you test it. Is this what you're looking for?


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532633

Highliting Duplicate Cells across multiple rows
 

From http://www.cpearson.com/excel/duplicat.htm

To create a named range, select the entire area of values, then type a
name (Range1) into the 'Name Box' on the formula bar.

-"Highlighting Duplicate/Unique Entries
We use Excel's Conditional Formatting tool to accomplish this. First,
highlight the entire Range1. Then, select the Conditional Formatting
tool from the Format menu: Format-Conditional Formatting.
Change the "Cell Value Is" option to "Formula Is" and enter the
following formula in the formula text box:-


Code:
--------------------
=IF(COUNTIF(Range1, A5)1,TRUE,FALSE)
--------------------


-Where A5 is the first cell in Range1. Then, click the Format button
and select the font or background color you want your cell formatted
with. Finally, click OK. Duplicate entries in Range1 will be formatted
as you selected. For example, if "Able" occurs twice in Range1, both
occurrences of "Able" will appear highlighted."-

To highlight unique entries, switch the formula to
Code:
--------------------
=IF(COUNTIF(Range1, A5)1,FALSE,TRUE)
--------------------



Scott


--
hannons
------------------------------------------------------------------------
hannons's Profile: http://www.excelforum.com/member.php...o&userid=33465
View this thread: http://www.excelforum.com/showthread...hreadid=532633

Highliting Duplicate Cells across multiple rows
 

The check to see the cell is blank isn't necessary. Therefore the
formula can be simplified to:

=COUNTIF($1:$65536,A1)-COUNTIF(A:A,A1)<0


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532633