Skip to main content

Highlighting Duplicates across multiple sheets



















Hi One and All,
This time I have come up with some conditional formatting stuff. The aim is to highlight the duplicates across multiple sheets and with in the sheet as well.
Assumptions:-
1. I am assuming that sheet names goes on like sheet1, sheet2, sheet3....
2. The Target Column No. is same in all the sheets. I mean, as in the attached example, its Column A which is being targeted in both sheets.


So here are the steps :-
1. Select the target column in the first sheet say Column A in sheet1 (as in my example).
(Note:- While selecting the column, It is important that first cell is being selected. Because it has relative effect on the Conditional Formatting Formula.)
2. Click Home tab--->Conditional Formatting---> New Rule--->Use a Formula to determine.
3. Enter the following formula there:-
=SUM(COUNTIF(INDIRECT("'Sheet" & ROW($1:$2) & "'!$A:$A"),$A1))>1
4. Select the appropriate highlighting way.
5. Click Ok.
6. You will notice some cells are being highlighted. It has three meanings:-
a) The Highlighted cell has duplicates in the current sheet itself. (Delete Ronaldo from second sheet. You will notice Ronaldos in the first sheet are still being highlighted.)
b) The Highlighted cell has duplicates in other sheet and not in current sheet. (Sachin, Neymar, Robben)
c) The Highlighted cell has duplicates in both the sheets. (Current and other one) (Ronaldo)

7. Perform the same Procedure on other sheets to highlight duplicates.
8. You can change the "'Sheet" & ROW($1:$2) part of the formula to produce sheet names of your requirement if they have any similarity and order like 1,2,3 or Jan, Feb, March etc.

Check the attached file:-
Now Case2:-






















That is if sheets names have no similarity or order.
List all sheet names in any sheet for reference as I have done in the second attached example and use the following conditional formatting formula.
=SUM(COUNTIF(INDIRECT("'" & Sheet1!$C$2:$C$3 & "'!$A:$A"),$A1))>1
It will do the same job as the previous one does but now Sheet Names are produced by a reference.

Check the attached file:-
Duplicates across multiple sheet 2.xlsx

I guess I have been able to demonstrate it well. However, suggestions and improvements are always invited so do write a comment.

Regards,
Vikas Gauatm
Labels:

Comments

  1. Unknown24 April 2018 at 04:13

    how about if we need to find duplicates with multiple columns and work sheets?

    ReplyDelete
    Replies
    1. Vikas Gautam24 April 2018 at 10:52

      Use powerquery for excel for better data manipulation.

      Delete

Post a Comment

Popular posts from this blog

Automation:- Sending Invitation to Meeting Using Excel VBA

Hello Everyone, In one of the previous post, I wrote about automating Sending Emails using Excel VBA. This time I have come up with a pretty similar code. The code below sends Outlook Meeting Invitations to recipients on one click. here is the Code:- Sub Send_Invite_Auto() Dim olApp As Outlook.Application Dim olApt As AppointmentItem Set olApp = New Outlook.Application 'Creating Outlook Session Set olApt = olApp.CreateItem(olAppointmentItem) 'Creating an Appointment With olApt .Subject = "Enter the subject here." 'Subject .Start = DateAdd("d", 5, Now) 'Enter Date + Time here. .Recipients.Add ("example@gmail.com") 'Recipient Name, Alias, or any other Attribute. .MeetingStatus = olMeeting 'olAppointmentItem with Meeting status olMeeting 'becom...
10 comments
Read more

Making Password Protected PDFs using Excel Vba and PDFtk Tool

Hi Everyone, This time, I have come up a VBA Code to generate Password protected PDFs using Excel. Actually, Excel Vba has .ExportAsFixedFormat Method to generate PDFs but this hasn't any Parameter which takes password to protect the PDFs. So I have used PDFtk Tool which provide Command Line Interface to make PDFs protected using Password. Actually, You can do various things using PDFtk Tool command line varying from creating, merging, Protecting and many other. So Download the PDFtk Tool from the following link:- Download PDFtk Tookit Here are the Steps:- 1. Install the PDFtk Toolkit. 2. Use the following code to Print or Export the Activesheet with a password.
8 comments
Read more