Solved

How can I identify a duplicate column using conditional formatting

Posted on 2016-09-05
6
36 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Suggested Solutions

Title # Comments Views Activity
incrementing rows  containing a string and number by 1 in excel. 3 27
Converting time 4 44
why cannot i download this excel file? 7 29
Conditional fromatting formula 29 33
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

730 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