Solved

How can I identify a duplicate column using conditional formatting

Posted on 2016-09-05
6
35 Views
Last Modified: 2016-09-25
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
Comment
Question by:agwalsh
  • 3
  • 2
6 Comments
 
LVL 33

Accepted Solution

by:
Rob Henson earned 250 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

by:agwalsh
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 30

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 250 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

Open in new window

EE-identify_duplicate_columns-vers-.xlsx
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 33

Expert Comment

by:Rob Henson
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 33

Expert Comment

by:Rob Henson
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 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41814554
The best answer chosen provides the stating point in order to resolve the question.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question