Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# How can I identify a duplicate column using conditional formatting

Posted on 2016-09-05
Medium Priority
40 Views
hi
In the attached file, how can I identify what columns have identical entries all the way down. I've experimented with countif/countifs/sumproduct...but haven't got anything that identifies it. Thanks.
identify_duplicate_columns.xlsx
0
Question by:agwalsh
• 3
• 2

LVL 34

Accepted Solution

Rob Henson earned 1000 total points (awarded by participants)
ID: 41784622
In your sample there are multiple duplicates, columns B, F & I and columns E & H.

In cell B11 I have put the following:

=CONCATENATE(B2,B3,B4,B5,B6,B7,B8,B9,B10)

In cell B12 I have put the following:

=COUNTIF(\$B\$11:\$J\$11,B11)

Copy both across to column J

Any value in row 12 greater than 1 indicates a duplicate column. You can then use cells in row 12 as basis for conditional formatting.
0

Author Comment

ID: 41784700
Got that working - thank you. :-) So how would I now highlight the columns with values greater than 1. Am attaching the completed file.
EE-identify_duplicate_columns-vers-.xlsx
0

LVL 33

Assisted Solution

Subodh Tiwari (Neeraj) earned 1000 total points (awarded by participants)
ID: 41784721
Based on your latest file, select the range B2:J10 and make a New Rule for conditional formatting using the formula given below and set the format as per your choice.
``````=B\$12>1
``````
EE-identify_duplicate_columns-vers-.xlsx
0

LVL 34

Expert Comment

ID: 41784931
I agree with Neeraj's suggestion for applying the Conditional Formatting (CF).

That does beg the question as to whether you want different groups highlighted differently. Simplest way would be to have different CF rules for each of the count values eg 2 = Red, 3 = Orange, 4 = Green.

However, in your sample, lets say Column D was also the same as E & H, you would then have two groups of 3 with duplicate values. Using the above, both groups of 3 would be formatted Orange so still wouldn't be able to determine the grouping.

How far do you need to take this?
0

LVL 34

Expert Comment

ID: 41786137
I took it one step further and wrote a UDF for identifying the groups.

See attached.

The UDF is used in row 17 and gives a comment for the matching columns.
EE-identify_duplicate_columns.xlsm
0

LVL 33

Expert Comment

ID: 41814554
The best answer chosen provides the stating point in order to resolve the question.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.