how to show whether 3 cells within a row match 3 cells in another

S

Swish7

Novice here...

How do I show or highlight, within a single worksheet, rows of cells that
have matching data with other rows in another part of the worksheet.

For example:
the worksheet has about 10,000 rows, and 11 columns.
I want to the formula to look through the entire worksheet and highlight for
me any single row which "matches" any other single row.
Whether or not it matches would be based on whether the data in columns B,
E, and J for that row match.
Column B is a number, Column E is a date, and Column J is another number.

025847 10/27/2008 012
025847 10/27/2008 013
025843 10/27/2008 012
025847 10/27/2008 012
025847 10/27/2008 012

For example, rows 1, 4, and 5 from the above example, I want those to show
up as matches.

Is this feasible?
 
D

DReid

simplest is usually the quickest...or I've just got a dumb way of doing
things. I'd create an extra column which equalled the text value of the cells
in question ie ="b"&"E"&"J" which will give you a unique value.
Then click on pivottable and arrange it so your unique value is the first
column and any number is the datatable, it'll group any duplicates for you

ta
DOug
 
S

Swish7

Another reason this can't work for me is that I would have to change the
formats of all the relevant cells from text or date to number in order for
them to be summable. Changing the format would change the values in those
cells, and render them useless.
 
H

harding via OfficeKB.com

try this formula in your conditional formatting: =AND(COUNTIF($A:$A,A1)>1,
COUNTIF($B:$B,B1)>1,COUNTIF($C:$C,C1)>1)
Another reason this can't work for me is that I would have to change the
formats of all the relevant cells from text or date to number in order for
them to be summable. Changing the format would change the values in those
cells, and render them useless.
I was hoping this could be done with conditional formatting..
[quoted text clipped - 30 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top