Solved

How can I identify a duplicate column using conditional formatting

Posted on 2016-09-05
6
32 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 32

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 29

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 32

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 32

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 29

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

803 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