highlight duplicates in rows in multiple not related sheets

[Alexander2020]

I got big help in a previous thread when I was need a code for highlight duplicates on column B and F on multiple sheets.
The code was:

Please Login or Register  to view this content.

What I need now is to have a similar/other code working when duplicates occurs on both columns, same row. sample.jpg
I have attached also a sample file made from my big survey.

[kev_]

Please clarify

What is a duplicate?
- are you reviewing only one sheet at a time ... duplicate ... is when ColumnD value = columnF value in the same row in same sheet?

Do multiple sheets have any impact?

[Alexander2020]

Please clarify

What is a duplicate?
- are you reviewing only one sheet at a time ... duplicate ... is when ColumnD value = columnF value in the same row in same sheet?

Do multiple sheets have any impact?

Thank you for asking. The duplicate is when ColumnD value = columnF value in the same row in same sheet. Multiple sheets have no impact. The sheets are built in the same way. So what happens in one sheet I only wish to be able to specify a multiple range of sheets instead of running the macro on each sheet. Maybe I should specify that the values on columnD and F are all strings type, not numeric.

[kev_]

thanks, will post code when back at PC

[kev_]

Try this

Please Login or Register  to view this content.

[Alexander2020]

Sorry kev_, It doesn't work... yet.
However I have updated my first post of the thread and added an image and made kinda a similar sample of my data.
(I should have done it in the first place, but I thought it would be enough just to explain.)

[Trebor76]

The duplicate is when ColumnD value = columnF value in the same row in same sheet

From your screen shot there are no rows in columns D and F that are the same in the Alpha tab so kev_'s code should correctly not colour anything in those columns

[Alexander2020]

From your screen shot there are no rows in columns D and F that are the same in the Alpha tab so kev_'s code should correctly not colour anything in those columns

As you can see in the image... Duplicate cells (same whatever text, sorry the text is not in English) on the columns D and F need to be highlighted only when they occur on the same row. I never said that the rows are identical other than in the columns D and F. How can I be more explicit? The image should be easy to understand or not?
kav_ made his code BEFORE my explicit image and sample file. This is why the code is not working.

[Trebor76]

The image should be easy to understand or not?

I was confused about the yellow highlights.

Thanks

[kev_]

Is it B and F (per post#1) or D and F (per other posts)

If the latter

Please Login or Register  to view this content.

For clarification - the above checks the value in column D against the value in column F and highlights column D if they match

[Alexander2020]

Dear kav_,

1) In post 1 I said I have a code that was finding duplicates on column B and F and highlighted them in yellow. I posted it as I thought it might help modify it for columns D and F, but (this is the hard part) I need that would highlight the duplicates only when they occur on the same row. I regret that I have posted that code... it created more confusion instead of helping.

2) Did you try your code on my sample file? Your code looks for duplicates on the same row, as D(value) = F(value), which is not the case. I need the duplicates on column D (inside that column) and duplicates on column F (inside that column), but highlight them ONLY when they occur on the same row.

In other words. On column D I have the names of several suppliers of social services. On column F I have names of the specific social service they provide (the name of the center they give shelter or food for elders, children etc). The original survey has been made without taking care of giving a unique key for each case (observation) before generating the files and so it ended with duplicates. Those duplicates mean that I have the same supplier giving the same service. Manually I have to check them. It is not always a mistake. So I cannot remove the duplicates, I need to highlight them.
If it was about only one workbook I would not bother to have a VBA code. I would add a column on the right side, concatenate column D and F and use the built-in conditional formatting to get the duplicates on that column. But I have 50 workbooks with 7 worksheets each of them and hundreds of cases on each sheet.

[Alexander2020]

There is a similar attempt in this thread: https://www.excelforum.com/excel-pro...using-vba.html

There are 2 codes posted there. The short one I tried to adapt it, but it does not work cause I have text in column F (not numbers) and I do not know how to change that code for my situation, despite i am able to change to refer to my columns. The long code posted on that thread is using a concatenation. It works on my sample file and do the job, but I cannot use it on my large files. It is adding two columns at the right side to make the concatenation, but it runs into a debug error and cannot delete those two extra columns. I cannot add columns to my data and anyway it does not run on my files, but only on my sample. This is the reason I wait maybe smart guys here can find something else.

[kev_]

I will amend the code when I understand what you want
How about ...

D2 = Association ABC
F2 = Center XYZ
Concatenation row2 = Association ABC|-|Center XYZ

D3 = Association DEF
F3 = Center XYZ
Concatenation row3 = Association DEF|-|Center XYZ

D4 = Association ABC
F4 = Center XYZ
Concatenation row4 = Association ABC|-|Center XYZ

Are row2 and row4 the duplicates that should be highlighted?

[Alexander2020]

Are row2 and row4 the duplicates that should be highlighted?

Yes! Exactly.

[Trebor76]

Hi Alexander2020,

Maybe a more efficient way but this works for me:

Please Login or Register  to view this content.

To make sure it worked I changed the colour for the duplications to green (the two lines that have vbGreen). You may want to change these to suit.

Regards,

Robert

[kev_]

Yes! Exactly.

thanks - will post amended code when back at PC this evening

[sintek]

My take on this one...

Please Login or Register  to view this content.

[Alexander2020]

@Trebor76... Your code worked 100% on the sample. When using it on the real big workbook it was going on an endless thinking and had to stop the Excel by force. I understood what it was doing. Since the range was "D:F" the code was thinking as duplicates all the empty cells found beyond the rows with data. These are not really empty cells cause they have some formula... but I found the solution.
I changed that range. Instead of

Please Login or Register  to view this content.

I put the

Please Login or Register  to view this content.

My data that need to search for duplicates starts from row 8... that is the reason I used "D8" in the range. All above are labels.

Now it works almost perfect! Some minor adjustment, if possible. Could you, please, modify the code to exclude empty (blanks) from considering them as duplicates as well (even they contains formula)? It colors part of the cells above the range (do not know why) and also give a message with the number of duplicates much higher than really found. I guess it counts those empty cells.

@kev_ I do not mind if you also come with a different code, but the above is already good enough, even Trebor76 would not improve it. Anyway I give reputation for all the people that try to help and big thanks to you all guys. You are all great guys!

[Alexander2020]

My take on this one...

Thank you, sintek. I'll try also your code!

[Trebor76]

I understood what it was doing. Since the range was "D:F" the code was thinking as duplicates all the empty cells found beyond the rows with data.

That piece of code finds the row across columns D, E and F. It doesn't go all the way down the column to the last row (1,048,576).

Could you, please, modify the code to exclude empty (blanks) from considering them as duplicates as well (even they contains formula)?

Done - there must be something in both columns for the code to check for duplicates.

It colors part of the cells above the range (do not know why) and also give a message with the number of duplicates much higher than really found. I guess it counts those empty cells.

That's suggests to me that there's a space(s) in the cell. Press F2 and see where the cursor is to check. I have used TRIM to exclude single spaces.

I have also added code to check what calculation method the workbook is using before the code runs, sets it manual then sets it back to its original setting after the code finishes. I also use a range variable to loop through the data.

I hope it all works out.

Regards,

Robert

Please Login or Register  to view this content.

[Alexander2020]

@sintek... Your code does not work on my huge file (it runs without any error, but no color on duplicates). Of course it works on the sample. My best guess is that your code does a concatenation on the right side in some specific place adding two columns, do the job and delete them. The problem might be that you should specify a place outside the data. I have data on few sheets on the right side until the "EI" column.

@Trebor76... The new code is going on the wrong direction. The first one was making color on what was supposed to make, but also on the cells D2 and F2 (cause they are empty). And what I said... much larger number of duplicates than I can see. Now, the new code does not run till the end. It gives run time error '9' subscript out of range on the line "For i = LBound(strDups) To UBound(strDups)".

[sintek]

If it works on sample but not actual....then...Your sample you uploaded is not a true reflection of your actual file...So upload proper sample so no time gets wasted...

[Alexander2020]

If it works on sample but not actual....then...Your sample you uploaded is not a true reflection of your actual file...So upload proper sample so no time gets wasted...

Indeed my sample proves to be not a true reflection of my actual files, but close enough. I have no choice. The files are 10 MB each and only 7 sheets in each workbook have the data similar to the sample and only on those I need to find the duplicates. The problem is that the data on column D and F is generated using Vlookup formulas from other sheets. It is a total mess and a failure of a country survey which I try to manually repair. I can't upload a real file, not only cause it is bigger in size than the limit, but mainly cause I would have to delete a lot of sensible personal info and lot of words to translate from Romanian to English. I left only few words in my sample and already caused confusion in the first place.

You all did your best and I am grateful and give reputation to all of you. I am certain that your code is as good as any other code that solved the sample file. My sample file is not a perfect reflection of reality, but it was good enough to get a 99% solution. For 1% more cosmetics than real issues, it is not worthy to try to make other samples which might also not be a true reflection of the real mess.
Only by chance, the first code that Trebor76 posted was good enough. If he (or any of you) wants to try something else or a new code, I am happy to try.

Therefore I'll wait 24 hours and if nothing new happens here I'll mark the thread as "Solved".

[kev_]

@sintek... Your code does not work on my huge file (it runs without any error, but no color on duplicates). Of course it works on the sample.

@Alexander2020
- your first post did not properly explain your requirements
- your sample file does not contain representative data

... and you expect a solution to your problem?

Perhaps an explanation of the "problems" in your data would have been helpful so that we understood what you were trying to overcome

It sounds like a nightmare of a job that you are undertaking - I wish you luck in sorting it

[Trebor76]

Therefore I'll wait 24 hours and if nothing new happens here I'll mark the thread as "Solved".

This is far more efficient:

Please Login or Register  to view this content.

Regards,

Robert

[Alexander2020]

Dear Robert, I feel already bad to see how much effort you put and rewrite the code from zero when it is not necessary. The last code might be far more efficient, but it is not finding the duplicates as my needs (on the same row), but finds all duplicates on column D and F without the condition that should be on the same row. I believe it should be easier to modify the range in the first attempt (post #15).
I write again what did work for me. I changed that range. Instead of

Please Login or Register  to view this content.

I put the

Please Login or Register  to view this content.

I do not know how to make the range to specify all together

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

That might solve the issue of running the code outside the range I need.
If you let the things as they are, no problem. The code counts wrongly the duplicates, displayed in the pop-up message (no problem... I like the feature, but I didn't ask for it... I can delete from the code or ignore that counting) and colors the D2 and F2 cells cause they are empty (as I mentioned before there are some labels and empty cells above row 8). I can live with that either.
Thank you so much for your time and efforts so far!

[sintek]

Perhaps if you took the time to provide a decent sample of actual setup...You will stop wasting precious time for incorrect code writing...

[Alexander2020]

Perhaps if you took the time to provide a decent sample of actual setup...You will stop wasting precious time for incorrect code writing...

I do not know what is relevant and what it is not relevant to include in a limited sized sample, what makes your code working with the sample and what details are causing the code to fail. Normally I should have provided the real file, but I explained why I can't do that. I asked a little help with the range. That is the last thing I wait for, if possible... and I close the subject. Sometimes we should accept things that are less than perfection, but good enough. I am satisfied by the result even you, my friend, seems to be upset.

[sintek]

seems to be upset

No...not at all...Unfortunate that we cannot solve even though your initial requirement has been fulfilled... Only means that your actual file has something that you did not previously mention or envisage...
After all...All the initial provided code Works with sample file...So you are missing something...

Seems you are close though...Good luck...

[Trebor76]

For me the confusion is here...

finds all duplicates on column D and F without the condition that should be on the same row

...whereas I think row should be actually be column. I have rewritten the code because on large datasets my original solution may stop working.

See how this goes where the duplicates in each column are coloured differently.

Please Login or Register  to view this content.

[Alexander2020]

The context of the confusing words was: "The last code might be far more efficient, but it is not finding the duplicates as my needs (on the same row), but finds all duplicates on column D and F without the condition that should be on the same row."
I merely stated what the second code you provided was doing. I need "with", not "without" the condition of the duplicates being on the same row. I said that the first code was doing what I need, except the described minor issues.
Now.. a picture is better than a thousand words. I attach below a new screenshot (after running your last code) and I attach a new sample (little bit closer to the real situation) including your last code inside:

[sintek]

Just as expected...This new sample is nothing like the initial sample setup...Common Newbie mistakes...
Once again this code does as expected on this New sample Upload...

Please Login or Register  to view this content.

Untitled.png

[Trebor76]

Assuming the following screen shot is what you're after, this will do the job:

Please Login or Register  to view this content.

[Alexander2020]

@sintek... Your code now works, but it also highlight in green two cells below the data in both column D and F.
@Trebor76... Robert... I don't wanna upset you, but the last code finds me like 20000 duplicates. I like your first code and I'll stick to that one.

I want to put an end to this. I have now two codes (one from each of you) that work for me with minor cosmetic issues. Trying to fix that is not worthy IMHO.
Thank you both for the efforts. I mark the thread as solved and wish you, guys, all the best! You are both genius to master the VBA coding!

[sintek]

@sintek... Your code now works, but it also highlight in green two cells below the data in both column D and F.

I don't think you know what you want...
On both occasions, the code provided the exact output as your two different uploaded samples...Not gonna waste any further time on this...Good luck...

Thanks for rep +

[Trebor76]

Hi Alexander2020,

Thanks for marking the thread as solved and the reps.

I have tweaked my original post from thread 15 above so it works better on large datasets:

Please Login or Register  to view this content.

I have also amended my code from thread 25. These two macros now produce the same amount duplicates (4 pairs or 8 in total). The colours I've used are different for each so you can see the results are the same as you should only be left with one colour (green or yellow, not both) after running each separate macro.

I would personally use the code from thread 25, but it's entirely up to you.

Regards,

Robert

[Alexander2020]

Dear Robert, This last code works flawless! Not only it doesn't make any of the past minor issues, but also counts perfect and I even didn't ask for this extra feature. The other one you amended (from post #25) also does the job, but it counts less. Anyway your work is beyond any expectations since I have already considered the problem solved.

[Trebor76]

The other one you amended (from post #25) also does the job, but it counts less.

It was counting the total the same but I just changed how total in the message box was being displayed so instead of counting each row that had been highlighted (say 8 as an example) it showed the number of pairs (4 in for this example). I have now changed the message in thread 25 to be the same as thread 36 so both should be in sync now.

I would personally use thread 25 because as it doesn't loop through each data row across the sheets within the workbook it's far more efficient and so better on large datasets. It's up to you.

Dear Robert, This last code works flawless! Not only it doesn't make any of the past minor issues, but also counts perfect and I even didn't ask for this extra feature.

You're welcome and thanks for the rep and kind words. I'm glad we finally got there

Robert