Solved

How can I identify a duplicate column using conditional formatting

Posted on 2016-09-05
6
37 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 31

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 31

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

636 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