Highlighting Duplicates across the work book in different ranges - with attachment

**max8719** · 01-30-2016, 07:36 AM

Hi Guys,

I have a challenge which I am struggling with. I need to highlight duplicates in rages across multiple sheets in a workbook.

There are 26 ranges per sheet which contain text values and the ranges are the same from sheets 1 to 10.

Just for instance if I duplicate a value in this sheet or any other sheet within the ranges it will highlight.

Ranges are:

D5:R5
D13:R13
D21:R21
D29:R29
D37:R37
D45:R45
D53:R53
D61:R61
D69:R69
D77:R77
D85:R85
D93:R93
D101:R101
D109:R109
D117:R117
D125:R125
D133:R133
D141:R141
D149:R149
D157:R157
D165:R165
D173:R173
D181:R181
D189:R189
D197:R197
D205:R205

I have attached the workbook for convenience.

Tracker.xlsx

Thanks in advance for your replies.

**sktneer** · 01-30-2016, 08:20 AM

Which row header may have the duplicate values Company Name or Name of DM?
Do you want to check for the duplicate in the same range in all the sheets? i.e. if you enter a value in the range R5:D5 on Player 1 sheet, do you want to check for duplicates in the range 'Player 1'!D5:R5 TO 'Player 10'!D5:R5 only.

It would be helpful if you can input some sample values and highlight them manually to show the desired output.

**max8719** · 01-30-2016, 08:33 AM

hi sktneer

The duplicate values would be in the company name header and the duplicates could occur anywhere within the ranges in all the sheets. If I enter a value in any of the ranges listed it would highlight in any of the ranges across the sheets.
To be fair I think the list above would reflect one big range. So lets just call it big range just for this purpose. If a duplicate occurs in players 1 to 10's big range it would conditional format the value to make the player aware that some one has already called that company previously.

Thanks for your support

**sktneer** · 01-30-2016, 09:11 AM

This can be achieved with VBA.
Open your file --> Press Alt+F11 to open VBA Editor --> Double click ThisWorkbook from the project explorer on left side --> Paste the code given below in the opened code window --> Close VBA Editor --> Save your workbook as Excel Macro-Enabled Workbook.

Please Login or Register  to view this content.

For detail, refer to the attached workbook.

**max8719** · 01-30-2016, 02:03 PM

I have tried the code but it seems to highlight all duplicates and is not focusing on just the company names, also tried to put a duplicate value in player 5 week 5 company name "jj" and player 1 week 1 company name "jj" and it didn't highlight.

**max8719** · 01-30-2016, 02:13 PM

Apologies I think I might have confused this matter sktneer. The whole range would be players 1 - 10 company names.

Regards,

**sktneer** · 01-30-2016, 03:16 PM

Please find the attached and see if this works now.
I have entered the described company name in Player1 and Player5 sheets and it's highlighting the company name on Player 5 sheet.

**max8719** · 01-30-2016, 03:32 PM

That is highlights beautifully however when I type james in "company name" and james in "name of DM" company name still highlights and if I put a 1 in company name it highlights too.

**sktneer** · 01-30-2016, 03:35 PM

Can Company Name and Name of DM be the same? If that is the case, this approach will not work.

**max8719** · 01-30-2016, 03:35 PM

Also if I put james in d5, e5 and f5 it highlights duplicates but when I delete the values after the conditional formatting stays

**max8719** · 01-30-2016, 03:40 PM

The likely hood of a company name being the same as the DM name is small but still a possibility.

**sktneer** · 01-30-2016, 03:41 PM

Which conditional formatting you are talking about? Is it one which is applied through the conditional formatting rule or one which is applied by the code in the cells with the duplicate values.
Always attach the workbook in the question when you say it is not producing the desired output.

**max8719** · 01-30-2016, 03:49 PM

Ok no problem I will do. I copied your code into the one attached.

Thank youTracker.xlsx

**max8719** · 01-30-2016, 03:52 PM

Tracker.xlsmApologies. It's this one attached

**sktneer** · 01-30-2016, 04:03 PM

Not sure why the blank cells were highlighted with red but when I deleted them the red color disappeared.
Also tweaked the code in case if you input the numbers for the company name which is not likely to be case.

**max8719** · 01-31-2016, 06:14 PM

Originally Posted by sktneer

Not sure why the blank cells were highlighted with red but when I deleted them the red color disappeared.
Also tweaked the code in case if you input the numbers for the company name which is not likely to be case.

Thank this works great

**sktneer** · 02-01-2016, 12:59 AM

You're welcome.

If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

You may also say thanks to those who have put their time and efforts to help you in this forum by clicking the Add Reputation link under their posts, another way to say thanks.

**max8719** · 02-01-2016, 04:17 AM

Hi sktneer,

Can this work if I share the workbook as I am getting a runtime error.

Please advise.

Many Thanks

Highlighting Duplicates across the work book in different ranges - with attachment

LinkBack

Thread Tools

Rate This Thread

Display

Highlighting Duplicates across the work book in different ranges - with attachment

Re: Highlighting Duplicates across the work book in different ranges - with attachment

Re: Highlighting Duplicates across the work book in different ranges - with attachment

Re: Highlighting Duplicates across the work book in different ranges - with attachment

Re: Highlighting Duplicates across the work book in different ranges - with attachment

Re: Highlighting Duplicates across the work book in different ranges - with attachment

Re: Highlighting Duplicates across the work book in different ranges - with attachment

Re: Highlighting Duplicates across the work book in different ranges - with attachment

Re: Highlighting Duplicates across the work book in different ranges - with attachment

Re: Highlighting Duplicates across the work book in different ranges - with attachment

Re: Highlighting Duplicates across the work book in different ranges - with attachment

Re: Highlighting Duplicates across the work book in different ranges - with attachment

Re: Highlighting Duplicates across the work book in different ranges - with attachment

Re: Highlighting Duplicates across the work book in different ranges - with attachment

Re: Highlighting Duplicates across the work book in different ranges - with attachment

Re: Highlighting Duplicates across the work book in different ranges - with attachment

Re: Highlighting Duplicates across the work book in different ranges - with attachment

Thread Information

Users Browsing this Thread

Similar Threads

VBA Code for populating cells from one Work Book A to another Work Book with condition

check for duplicates in the whole work book

[SOLVED] Highlighting duplicates between 2 ranges

Linking specific Excel ranges within a work book into a Word Document

Work Book as an Attachment

[SOLVED] Locate duplicates in a work book by using conditional formatting

Locate duplicates in a work book by using conditional formatting

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions