If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 ........ |
#2
|
|||
|
|||
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 ....... |
#3
|
|||
|
|||
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 ....... |
#4
|
|||
|
|||
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 ....... |
Thread Tools | |
Display Modes | |
|
|