A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Find and Highlight duplicates in 5 non-adjacent columns



 
 
Thread Tools Display Modes
  #1  
Old February 23rd, 2009, 11:21 PM posted to microsoft.public.excel.newusers
GVPro
external usenet poster
 
Posts: 2
Default 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  
Old February 24th, 2009, 12:10 AM posted to microsoft.public.excel.newusers
Shane Devenshire[_3_]
external usenet poster
 
Posts: 3,333
Default 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  
Old February 24th, 2009, 12:37 AM posted to microsoft.public.excel.newusers
Max
external usenet poster
 
Posts: 8,574
Default 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  
Old March 6th, 2009, 03:25 AM posted to microsoft.public.excel.newusers
GVPro
external usenet poster
 
Posts: 2
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:50 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.