Find and Highlight duplicates in 5 non-adjacent columns

  • Thread starter Thread starter GVPro
  • Start date Start date
G

GVPro

I have series of serial nnumbers in five non-adjacent columns.
Ineed to find, and, if possible, highlight them.
I am using Excel 2003 at work and 2007 at home.

EX:
A B C D E F G H
I J

1 31686 ....... 21213 ....... 37801 ....... 55555 ....... 78613
........
2 37814 ....... 80081 ....... 67766 ....... 54455 ....... 84400
........
3 55555 ....... 60550 ....... 41223 ....... 10002 ....... 27177
........
4 43477 ....... 77772 ....... 43434 ....... 94867 ....... 29525
........
5 69362 ....... 24543 ....... 55555 ....... 99323 ....... 63122
........
 
Hi,

Do the duplicates need to be on the same row, what in the other columns, are
they number which could be the same as those in the 5 original columns?

The easiest way to do this would be a conditional formatting formula of:

=COUNTIF($B$1:$J$9,B1)>1

But this might not work depending on what was in the other columns. If the
other columns don't contain numbers than

=AND(COUNTIF($B$1:$J$9,B1)>1,ISNUMBER(B1))

If that doesn't work because of the content of the intermediate columns you
can use

=AND(SUM(($B$1:$B$9=B1)+($D$1:$D$9=B1)+($F$1:$F$9=B1)+($H$1:$H$9=B1)+($J$1:$J$9=B1))>1,ISNUMBER(B1))

In this case you need to make sure that the active cell is B1 when you put
this in the conditional formatting. And you only want to select your 5
columns not the other ones.
 
One other play to try

Select the entire range, eg select A1:J10 (A1 active),
then apply CF using Formula Is:
=AND(A1<>"",COUNTIF($A$1:$J$10,A1)>1)
Format to taste > ok out
Adapt the range to suit

If above works, high-five it here, click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
 
Duh,
You've got a rank amature here.
It only took me till now to get it to work, that's:
=AND(SUM(($B$1:$B$9=B1)+($D$1:$D$9=B1)+($F$1:$F$9=B1)+($H$1:$H$9=B1)+($J$1:$J$9=B1))>1,ISNUMBER(B1)).
All my duplicates show up in the 5 columns, both up and down, and, left and right.
Shane, thank you again for your help, it is very much appreciated.
and I apologize for taking so long to reply.
One more thing. If I have to do any more of this, I could really use a good tutorial >or 3.
Would you have any suggestions?
Later, GVPro.
Click to expand...

--
 
Back
Top