Find and Highlight duplicates in 5 non-adjacent columns
 

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
........

Find and Highlight duplicates in 5 non-adjacent columns
 

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.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"GVPro" wrote:

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
.......

Find and Highlight duplicates in 5 non-adjacent columns
 

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
---
"GVPro" wrote:
I have series of serial numbers 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
.......

Find and Highlight duplicates in 5 non-adjacent columns
 

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.

--
"Shane Devenshire" wrote:

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.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"GVPro" wrote:

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
.......