Find duplicates in column pairs??

squirrellydw

Board Regular
Joined
Apr 2, 2015
Messages
85
Office Version
  1. 365
Platform
  1. Windows
Is there an easy way find duplicate names the show up in different columns? The tricky part is it would have to cover columns in pairs for example if John Doe is listed in column B and K, or C and L, or D and M, or E and N, or F and O, or G and P, or H and Q. I have it working now but I had to make a rule for each column? Just trying to find an easier / better way.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Suggest you use XL2BB and provide us with some sample data of 10-15 records. Then mock up a solution manually so that we understand completely your issue.
 
Upvote 0
Can't install it since it's a work computer. I'm currently using CF for each column and if the same name shows up in two columns, for example B and K it will highlight the name in red. Just looking to see if there is a better way or easier way to do it
 
Upvote 0
Is this what you want? It only requires 2 CF formulas, one for each block.

25 02 03.xlsm
ABCDEFGHIJKLMNOPQ
1
2LHKOLZUXVDAEURUXSXFOZSDGMEKN
3XXXQIYZQKFPOJLSFJLCSUVBENUNA
4YVFEQIQCXBFWGDKXNSVHIOVKNFJL
5PVIOROPLLQVUCPZSIBRFJISXCYGX
6WYIBSJQBHAXKWRUNYXKXBUWQKTBE
7FZCIYPEONCCWFJBNTJQVQFVWGCIG
8PSQMEVKDIQZSQYGMFTCMYWZIPORE
9KMIJDAYMRBMLAMDRWSMOXCYCPOCR
10WGWOXVOGNAAISGLOCKOIQJNGPOES
11BJUBTPSCQUJIRHOSZMHKHKLDPOLO
12WNZOSSJSFAIEWTQDVKJMXTKTMJSZ
13PAAKFDUUTUXOQYFBFPQFIRVNMKHG
14ISOVBVVINPTGNZUQFYDTRIJSNUDD
15GPHFGVMDYHQJGKRLXJMYTBANSEPO
16CMLLXSFGTYWXFLSCMIVHZQKEXOVB
17YFNPBXVONJOCZZJFWBDCCDUOJEJZ
18FFHGLHJEPEABEYHAFPGPESJRFDRO
19PADUUKGJPZKPTMMSKOTEIBVLGYQY
20SYDYVCWPHPQLFBUXQQOKBLXXMZUS
CF Dupes
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K2:Q20Expression=MATCH(K2,B:B,0)textNO
B2:H20Expression=MATCH(B2,K:K,0)textNO
 
Upvote 0
Cheers. Here is another option where I have applied just one rule to the whole range (B2:Q20)
The 100 in the formula needs to be a row number big enough to cover your whole data.

25 02 03.xlsm
ABCDEFGHIJKLMNOPQ
1
2LHKOLZUXVDAEURUXSXFOZSDGMEKN
3XXXQIYZQKFPOJLSFJLCSUVBENUNA
4YVFEQIQCXBFWGDKXNSVHIOVKNFJL
5PVIOROPLLQVUCPZSIBRFJISXCYGX
6WYIBSJQBHAXKWRUNYXKXBUWQKTBE
7FZCIYPEONCCWFJBNTJQVQFVWGCIG
8PSQMEVKDIQZSQYGMFTCMYWZIPORE
9KMIJDAYMRBMLAMDRWSMOXCYCPOCR
10WGWOXVOGNAAISGLOCKOIQJNGPOES
11BJUBTPSCQUJIRHOSZMHKHKLDPOLO
12WNZOSSJSFAIEWTQDVKJMXTKTMJSZ
13PAAKFDUUTUXOQYFBFPQFIRVNMKHG
14ISOVBVVINPTGNZUQFYDTRIJSNUDD
15GPHFGVMDYHQJGKRLXJMYTBANSEPO
16CMLLXSFGTYWXFLSCMIVHZQKEXOVB
17YFNPBXVONJOCZZJFWBDCCDUOJEJZ
18FFHGLHJEPEABEYHAFPGPESJRFDRO
19PADUUKGJPZKPTMMSKOTEIBVLGYQY
20SYDYVCWPHPQLFBUXQQOKBLXXMZUS
CF Dupes (2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:Q20Expression=MATCH(B2,OFFSET(B2,-ROW(B2)+1,9-18*(COLUMN(B2)>9),100),0)textNO
 
Upvote 0
I have it working now but I had to make a rule for each column?
I'm guessing you want the duplicates to be highlighted using Conditional Formatting? If so, consider the following example...

For columns B to H, rows 1 to 20, start by selecting range B1:H20, then go to Conditional Formatting > New Rule > Use a formula to determine which cells to format:
Excel Formula:
=MATCH(B1,INDEX($1:$20,0,COLUMN()+9),0)
Then, set the desired cell format and click OK to create it.

Repeat for columns K to Q, rows 1 to 20, by selecting range K1:Q20 and using a similar formula:
Excel Formula:
=MATCH(K1,INDEX($1:$20,0,COLUMN()-9),0)

Edit: sorry @Peter_SSs I didn't see either of your replies before I clicked post. It's very similar.
 
Last edited:
Upvote 0
thank you, I got it working
 
Upvote 0

Forum statistics

Threads
1,226,364
Messages
6,190,539
Members
453,611
Latest member
JRM59

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top