• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Find Duplicates In Multiple Sheets|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Find Duplicates In Multiple Sheets|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosFind Duplicates In Multiple Sheets
sp_PrintTopic sp_TopicIcon
Find Duplicates In Multiple Sheets
Page: 123Jump to page
Avatar
Shane O'Sullivan
Member
Members
Level 0
Forum Posts: 24
Member Since:
March 3, 2020
sp_UserOfflineSmall Offline
1
March 3, 2020 - 9:38 pm
sp_Permalink sp_Print

g'day guys

I have what I think is a unique problem I am trying to resolve and have not been able to find exactly the required answer anywhere online. Lots of partials which allow me to do some of it, but nothing I can use for the entire task

I have a workbook which has 3 sheets in it. My boss has asked if we can find any duplicate entries in the 3 the sheets and have the duplicate copied to the next available row of the newly created 4th sheet. Sounds easy enough

The 3 sheets are SBLay, FALays 1 & FA Lays 2 and I have also created Named Ranges in all 3 sheets for columns A, B & H, which is how duplicates will be determined. Unfortunately in Excel for Mac, it is not possibly to have the same range name and have it allocated for specific sheets, so have had to name them logically. Having only one Range Name for Date, one for Time and one for Horse would have been easier, of course

As I said, to determine duplicates, only columns A (Date), B (Time) & H (Horse) will be used, but to make it easier, I named the ranges as follows:

For Date - DateFALAYS1, DateFALAYS2 & DateSB

For Time - TimeFALAYS1, TimeFALAYS2 & TimeSB

For Horse - HorseFALAYS1, HorseFALAYS2 & HorseSB

I am hoping what I have done is create dynamic Named Ranges, meaning they will grow as the entries grow daily. 

In English, the idea is as follows:

IF data in any row of the sheet FA Lays 2 in ranges DateFALAYS2, TimeFALAYS2 & HorseFALAYS2

matches data in any row of sheet FA Lays 1 in ranges DateFALAYS1, TimeFALAYS1 & HorseFALAYS1 or data in any row of sheet SBLay in ranges DateSB, TimeSB & HorseSB 

THEN

Copy the duplicate row to the next available row of sheet Confirmed Lays

I do hope this is possible and thanks so much in advance

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
March 3, 2020 - 11:40 pm
sp_Permalink sp_Print

To clarify

1) If A&B&H from SBLAY appear on FA Lays 1 and/or FA Lays 2 you want it copied to Confirmed Lays
2) If A&B&H from FA Lays 1 appear on SBLAY and/or FA Lays 2 you want it copied to Confirmed Lays
3) If A&B&H from FA Lays 2 appear on SBLAY and/or FA Lays 1 you want it copied to Confirmed Lays

So each sheet is looks up against each other?

If so can you please check the Confirmed Lays sheet on the attached? I used Advanced filter and copied the result - manual at the moment but could be automated it needed.

Purfleet 

Avatar
Shane O'Sullivan
Member
Members
Level 0
Forum Posts: 24
Member Since:
March 3, 2020
sp_UserOfflineSmall Offline
3
March 4, 2020 - 12:52 am
sp_Permalink sp_Print sp_EditHistory

hi Purfleet

Thanks so much for getting back and for the file. I can't do a complete comparison to see if they are all correct, but it does looks to have moved 6 entries from the FA Lays 1 sheet, rather than copied them. 

So for the clarification you asked for, yes, if there are any duplicates across any of the 3 sheets, the duplicate row will be copied to the Confirmed Lays sheets. 

It doesn't need to be automated necessarily, but I would prefer it is done without the use of a helper sheet, so whether it needs to be a macro or VBA would be ideal. The main reason is the volume of entries. The workbook I supplied only had a few left in the sheets, but there are some thousands, so to do it manually wouldn't be right and the use of the helper sheet is not something that is feasible for this process.

Take care and thanks so much

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
4
March 4, 2020 - 6:01 am
sp_Permalink sp_Print sp_EditHistory

Try the attached - Just run the LayRunOrder Macro

It is automated using advanced filter with all sheets checked against each other

SBLay v FA Lays 1
SBLay v FA Lays 2
FA Lays 1 v SBLay
FA Lays 1 v FA Lays 2
FA Lays 2 v SBLay
FA Lays 2 v FA Lays 2

This leads to some duplicated records on the 3 lookup fields, but other columns are not the same so not sure if you want to keep both or just one. Example below - all columns are the same until we get to SP - it would be quite easy to add a remove duplicates line if you only want 1.

Date Time # of Runners VDW Win % VDW Pl % Track Class Horse PR VDW Form 5278 CFR RnkPFP
4/11/2019 14:00   84.78 97.83 Kempton (A.W) 5 Colonel Slade ** 100 10 4
4/11/2019 14:00   84.78 97.83 Kempton (A.W) 5 Colonel Slade ** 100 10 4
Jockey/Trainer Place SR Forecast Odds Forecast Rank SP BSP BSP Place Result Win P&L Place P&L Win Ongoing P&L Place Ongoing P&L  
0 2.88 1 3752 4.50 5.30 2.18 L 0 0.00 -277 -94.32
0 2.88 1 4.5 5.3 2.18 L 0 0.00 -165 -119.00  

The macro finishes up by deleting the temporary Criteria sheet and jumping to the Confirmed Lays Sheet

Avatar
Shane O'Sullivan
Member
Members
Level 0
Forum Posts: 24
Member Since:
March 3, 2020
sp_UserOfflineSmall Offline
5
March 4, 2020 - 11:02 am
sp_Permalink sp_Print

cheers Purfleet

Thanks so much for your efforts

Yes, the only extra column is the Race Value column. I will be trying to duplicate this all to add to the other sheets which are for backs, the opposite of lays. 

I added the LayRunOrder macro to my My Macros toolbar, but it fails at almost the last hurdle. The debugger stops on this line Worksheets("FA Lays 1").ShowAllData which is in the final comparison - FA Lay 2 vs SBLay. Any thoughts on what would be causing this?

I am also wondering how this will not copy all of the same data again when run each day. As new data is copied to the file, the macro will need to be run and any new duplicates added to the Confirmed Lays sheets. Do you envisage any issues with that?

cheers Purfleet. It is 99% there; just failing right on almost the last line of the final comparison

Regards

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
6
March 4, 2020 - 4:45 pm
sp_Permalink sp_Print

I added the LayRunOrder macro to my My Macros toolbar, but it fails at almost the last hurdle. The debugger stops on this line Worksheets("FA Lays 1").ShowAllData which is in the final comparison - FA Lay 2 vs SBLay. Any thoughts on what would be causing this?

Works fine on both my Home and work PCs. When it errors and the debugger opens, check the FA Lays 1 worksheet and it should be filtered - if so this line should just turn off the filter. Did you step through the code and select the wrong sheet manually?

I am also wondering how this will not copy all of the same data again when run each day. As new data is copied to the file, the macro will need to be run and any new duplicates added to the Confirmed Lays sheets. Do you envisage any issues with that?

It will copy over the same data every day - i wasn't aware this would run daily, but all i would do is remove duplicates on all columns as a last step, then even if it copies over the same data it will be removed. This might be come an issue with 1000's of rows if it takes too long but i wouldn't think so with this data

Purfleet

Avatar
Shane O'Sullivan
Member
Members
Level 0
Forum Posts: 24
Member Since:
March 3, 2020
sp_UserOfflineSmall Offline
7
March 4, 2020 - 6:01 pm
sp_Permalink sp_Print

ah, that may be the issue, as I did indicate mine is Excel for Mac. There may be something which causes this. It is a 1004 run-time error saying ShowAllData method of Worksheet class failed. When I check the FA Lays 1 worksheet, it is not filtered in any way at the time the debugger opens. 

Did you step through the code and select the wrong sheet manually? Yes, I stepped through the code and the only line which gives an error is the one indicated. 

I am no coder, but I noticed that in SBLay and FALays1, this line of code is present, but in FALays2, which is where it fails, it is absent

The line is:

Worksheets("Criteria").Range("2:" & critLR).ClearContents

I added that to the VBA in FALays 2, but it still failed at the same point, so it wasn't the culprit; just thought I'd give that a try, so in the end, I can't shed any light on it.

Yes, the copying of all data repeatedly will be an issue, as there will be thousands of rows and are already hundreds in some. I trimmed the workbook down substantially and hid another 7 sheets in it which were not to be part of this particular task. The small amount of data supplied was just to try and get something workable, but right at the moment, it falls over at the same line each time.

Thanks so much for what you have supplied. it is very close, I know it, but something is amiss right towards the end

cheers

Avatar
Shane O'Sullivan
Member
Members
Level 0
Forum Posts: 24
Member Since:
March 3, 2020
sp_UserOfflineSmall Offline
8
March 4, 2020 - 6:27 pm
sp_Permalink sp_Print sp_EditHistory

Howdy Purfeet

OK, I did some reading and found that error usually occurs "when you try to remove an applied filter when there is not one applied". So I thought, OK, it is trying to show all data and all data in that sheet is already showing; I simply deleted that line and it works as it should, apart from copying what will end up being hundreds and even thousands of rows which have already been done.

Is there a way to check if the data found in those 3 sheets already exists in Confirmed Lays and only copy new data?

At least for the moment, it is doing the main part of its task, so I thank you very much for that

Regards

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
9
March 5, 2020 - 2:50 am
sp_Permalink sp_Print

I am no coder, but I noticed that in SBLay and FALays1, this line of code is present, but in FALays2, which is where it fails, it is absent

The line is:

Worksheets("Criteria").Range("2:" & critLR).ClearContents

It is in there once per sheet as the criteria is the same when comparing (for example) SBlays vs FA Lays 1 and FA Lays 2 - all that it is doing is clearing the details in the criteria sheet.

Worksheets("FA Lays 1").ShowAllData isnt the error as such, the issue seems to be that the worksheet is not filtered

If i add a break point to that line and run the code i can go to the FA Lays 1 worksheet and see that the rows are filtered and if i then go back to the code and press f8 the line executes and the FA Lays 1 worksheet is unfiltered

showalldata.PNGImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage showalldata.PNG (66 KB)
Avatar
Shane O'Sullivan
Member
Members
Level 0
Forum Posts: 24
Member Since:
March 3, 2020
sp_UserOfflineSmall Offline
10
March 5, 2020 - 1:36 pm
sp_Permalink sp_Print

Yes you're right, FA Lays 1 is definitely not filtered when that line is run, though when I add a breakpoint to the code, nothing changes for me. FA Lays 1 looks complete either with or without the breakpoint. Again, maybe it is a quirk of running Excel on a Mac, I can't say. 

Any workaround to have the code work correctly mate?

Thanks so much for taking the time

cheers

Avatar
Shane O'Sullivan
Member
Members
Level 0
Forum Posts: 24
Member Since:
March 3, 2020
sp_UserOfflineSmall Offline
11
March 5, 2020 - 4:42 pm
sp_Permalink sp_Print

One thing I did just notice in your screen grab is that the line I mentioned yesterday is present in your image, but is not present in the code that came in the file I downloaded from here. That is the line 

Worksheets("Criteria").Range("2:" & critLR).ClearContents

Having said that, it still makes no difference to add that line where you show it in your image, which is what I had done yesterday. With the breakpoint on the same line as yours, when it stops and I go and look at the sheet, FA Lays 1 has nothing filtered.

cheers

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
12
March 6, 2020 - 12:22 am
sp_Permalink sp_Print

Have you run the macro in just the uploaded file? So just as it was on my PC?

Avatar
Shane O'Sullivan
Member
Members
Level 0
Forum Posts: 24
Member Since:
March 3, 2020
sp_UserOfflineSmall Offline
13
March 6, 2020 - 11:55 am
sp_Permalink sp_Print

I had and it worked fine, so checked again this morning and the same. Here's the hitch, I copied across all of the data from the real workbook for SBLay, FA Lays 1 & FA Lays 2, then ran the macro again and it fell over at the same spot. could it maybe have issues with the volume? FA Lays 2 has around 4,000 rows

Or does the position of the tabs make any difference in the code? It looks to be only referencing the sheet name, so can't imagine that sheet position in the  workbook would matter

Really not able to she any further light on it. If I could upload a larger file, you'd be able to see but the restriction is 1Mb

cheers

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
14
March 6, 2020 - 2:38 pm
sp_Permalink sp_Print

Can you zip the file using 7zip? It might compress it enough to get to under 1mb.

Or can you zip it, then put it in a free dropbox account and share the link?

purfleet

Avatar
Shane O'Sullivan
Member
Members
Level 0
Forum Posts: 24
Member Since:
March 3, 2020
sp_UserOfflineSmall Offline
15
March 6, 2020 - 3:23 pm
sp_Permalink sp_Print

It didn't shrink much...from 2.6Mb to 2.3Mb, but here is the DB link

https://www.dropbox.com/s/i1kf.....m.zip?dl=0

cheers

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
16
March 6, 2020 - 7:09 pm
sp_Permalink sp_Print

Okay will look tonight when I get home as I can't download at work

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
17
March 7, 2020 - 3:47 pm
sp_Permalink sp_Print

Morning Shane

The workbook you uploaded does not have the Macros i created in it - are you keeping both spreadsheet open while running the Macro? (i.e the orginal one and the one you downloaded from here?

If so depending on the selected workbook when you start eh code could well be executing on the workbook i uploaded.

Can you copy module 1 from the workbook i uploaded to your data workbook and try again?

Purfleet

Avatar
Shane O'Sullivan
Member
Members
Level 0
Forum Posts: 24
Member Since:
March 3, 2020
sp_UserOfflineSmall Offline
18
March 7, 2020 - 9:48 pm
sp_Permalink sp_Print

Howdy Purfleet

You're right, I had actually saved the contents of module 1 to a module in the Personal Macro Workbook, then simply created a button on the toolbar and ran it from there. Should it not be the same thing running it that way?

Just now I opened your original file and copied the contents of module 1 to a new module in my file. I then added it as a button on the My Macros toolbar I have. When I ran it, the same thing.

I also tried double clicking the Confirmed Lays sheet and pasting the contents into that and running it that way, but still the same result. 

To your question, no, I do not normally have your original file open at he time. Each and every time I run it I am in my file and simply click a button on the toolbar to run the macro. Each time it fails on the same line. 

Would it help to have the file now resent with the macro saved in it to see if it makes a difference at your end? I still wonder if it not some quirk between Excel for Windows and Excel for Mac

cheers 

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
19
March 7, 2020 - 11:51 pm
sp_Permalink sp_Print

It may not make any difference if you added it to a Personal Macro workbook, it really depends if I used any reference to 'thisworkbook' or if I referred to them by name.
Personally, I would not add a Macro to the PMW unless it is a macro designed to work with any sheet - for example a macro that set page orientation and margins. I would always add the Macro the actual workbook

I will have another run through the code and maybe tighten up a few bits. Are all of the worksheets to be compared to each other (except Confirmed lays)?

Purfleet

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
20
March 8, 2020 - 10:11 am
sp_Permalink sp_Print

https://1drv.ms/x/s!AomqbYFKl5.....A?e=4Nj833

I have changed the file around a bit and made each worksheet loop and filter on each other, so it is a bit more efficient as we don’t need to have code for each sheet.

Just download the workbook and run the Timer or LayRunOrder (the only difference is the timer one reports the time taken to execute in VBA)

Only use the attached as the test file as i have deleted all the data from Confirmed Lays and also some other anomalies in the worksheets which will need fixing before you use the file in anger.

1) on some sheets there are hidden columns and rows - the macro unhides all of these each time it is run
2) the safe bets sheet has data in columns S & T from rows 335 to 916
3) the debut destroyer sheet has an extra column which means that the horse is in I and not H - Race type needs to be removed (or at least moved to the last column) so that all the headers for each sheet are the same.

When I ran the Macro it took about 22 seconds start to finish and copied over 10000 records to Confirmed lays.

I think the Macro is doing what you asked for, but you will need to test it extensively as I do not understand the data at all and I have also never used a Mac

Let us know how you get on

Purfleet

Page: 123Jump to page
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Brian Pham, Chandler Davis, Valentyn Kristioglo
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
drsven
Annie Witbrod
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27212

 

Member Stats:
Guest Posters: 49
Members: 31880
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.