hi mate
For dupes, the only rows which are of any relevance are A, B & H. One those 3 rows match, you have a duplicate entry. I am relatively sure the top bit I have now isolates the 3 sheets; that is the dim sheetsArray and down to sheetObject. I am hoping that now only addresses those sheets.
The data in the 3 columns in question is identical in format; A is Custom dd/mm/yyy, B is Custom hh:mm and H is General and just text...a name.
The one problem area is FA Lays 2 which always needs to have the Race Value column; that is it needs that in its own sheet, but not necessarily in the Confirmed Lays sheet, so maybe it could be hidden first, then unhidden; I reckon I can sus that one. In the Confirmed Lays sheet, I have a column for Race Value, but if we are going to hide that column in FA Lays 2, then it is easy enough to delete it altogether from the Confirmed Lays sheet.
The code I put above was found in a thread online and I tweaked it to try and fit, so it looks to be right, but I guess would only know when it is all up and running.
I mean, I like the idea of the Option Explicit section listing everything and then the section where each macro was called. In essence, it was all great, just it looped through 10 sheets 10 times, so 100 passes, I guess. With only 3 sheets referenced, the time will reduce substantially, as will the selections.
My initial thought was to replace the CritWS references with sheetObject or sheetsArray so it only referenced those 3 sheets listed, but you will know more than I about that.
https://send.firefox.com/download/7059ec2076469812/#eKYKEubgxijlbqlQCP4aSA That is the link mate. The original file has had some massaging with input from stackflow, but in then end, it was not so easy to exlude the other 7 unnecessary sheets
Thanks so much for your time
Sorry shane, the link has expired. Can you keep it live for a few days in case i miss it again
Purfleet
apologies mate. https://send.firefox.com/download/1a5de79ba6fd74fe/#ux6UlEvPFI8sd2Y4KXrULg
This one has 7 days on it
cheers
Howdy Purfleet
I just thought to check in and see if you were able to successfully get back into the UK alright and everything is going OK; strange times at the moment.
I know you will have many more pressing things on your plate at the moment, so really just checking if you were able to retrieve that file at all. It had 7 days expiry on it, but that was right about when you were expecting to travel.
Really just touching base, but can upload that file again if and when you have the time to take a look
Stay safe over there
cheers
Thought about this last night and thought i would have a look over the long weekend whilst in lockdown. Work has been fairly busy over the last week or so, so not really got the chance.
Check in over the weekend and we should have something
ha! You're a marvel mate. So you got back in alright, which is good.
Yeah, been busy here as well; just backtesting and trying to develop workable systems.
No stress at all. When it suits and you have time
Take care
Morning Shane
I thought we should start again - for 2 reasons - 1) i cant remember anything we did/spoke about before 2) I cant be bothered to read the millons of posts in this thread!
We can also try and do this in steps and solve any issues as we go along.
I have created 4 Subs, to start the Macro you only need to run ShaneXRun
ShaneX_Setup
Finds the last row on each sheet, sets the ranges on the columns we are comparing, clears up any legacy filters or criteria sheets and then adds in a new criteria sheet.
ShaneX_sbLay_v_faLay1()
Copies the data in A, B & H from the Safe Bets Lay worksheet to the crtieria worksheet. It then runs advanced filter to find duplicates and copies them to Confirmed Lays
ShaneX_Cleanup()
Checks for duplicates in column A,B & H and deletes the row, then it goes through and clears up any legacy filters or criteria sheets.
Questions
are you okay with ShaneX_Setup and ShaneX_Cleanup? They are fairly basic housekeeping, so should be straightforward enough
Is ShaneX_sbLay_v_faLay1 doing what you expect? I couldnt actually find any duplicates on on this sheet so added the row in yellow so we would have somthing to copy over.
When we copy the row from FA Lays1 to Confirmed Lays the columns are slightly different with columns P appearing (Race Value). Is this correct, if so i will just move the pasted data from Column p over 1 cell?
If we can get this part right i can crack on with the next comparison
Purfleet
https://send.firefox.com/download/baaa12e7464a1c02/#-TyDq3Hv-X8beNbDY-JPvg
g'day mate
It all seems spot on in theory. It certainly copies that yellow coloured one across because, as you said, there are no duplicates between those 2 sheets.
re the different columns, I added the Race Value one just to accommodate the FA Lays 2 sheet, but you know what, maybe it is best to just hide that column before the comparison, as it is the only sheet which has that extra column and it is necessary for that particular system.
So the first thing any comparison that includes FA Lays 2 could do is to hide P (Race Value) and then everything will match up with columns.
It all looks right, but you're more the coder than I. I am trying to learn each bit as I go, though, so I understand what each thing is doing. Down the track I will replicate this and exclude these 3 sheets and compare the other 7. They will go in Confirmed Backs once I have a go at it. It will be educational to give it a try, as I can see how it is all structured so far.
May I ask how the ShaneX_Cleanup removes the dupes, as I don't see any code to handle that?
Probably the last thing would be the checking of existing duplicates in the Confirmed Lays sheet. As this will be run daily, it would be best if only new entries are copied across, though I guess the removal of dupes will handle. Is that what the Check for old sheets & filters is doing, though?
It seemed to run very fats just doing those 2 sheets
Thanks again for all your help. Stay safe mate
Hi Shane
Good spot on the remove dups part - I uploaded an older version of the worksheet with that line missing.
I have now added that part and also added 3 more yellow dups and they all seem to be working.
Removing the duplicates from the Confirmed lays happens every time it is so everything will be copied over, every day and then removed based on the 3 columns. I dont think that this will cause to much delay, but if it does we can address that later.
With regards to the extra column P i have amended the code so rather than copy over the whole row we now copy over columns A to O to A to O then P to W to Q to X.
Have a play with it and if there are any issues.
If all is good we can then go on to the next comparison - Safe Bets lay v FA Lays 2?
I have purposely not created loops to do the comparison to be the code simple, although it will be longer to write
Purfleet
https://send.firefox.com/download/ee1b662e07d572ab/#SHY19yu_Q0nOCSJWdJ7GHA
howdy
Have run that a few times and it seems to do exactly what it needs to. The yellow duplicates are copied across perfectly
cheers
Morning Shane
I have added Safe Bets lay v FA Lays 2 - can you please check.
If all okay, what is the next comparison?
Purfleet
https://send.firefox.com/download/3608c6508dc07ddb/#8-HcHewRCzFIslMoOx1neA
Hi Purfleet
That seems to do the job. Apart from the yellow ones you created, there were only 2 duplicates between those 2 sheets and they are copied across perfectly and quickly
That is Safe Bets Lays vs FA Lays 1 and Safe Bets Lays vs FA Lays 2 done. The final comparison is FA Lays 1 vs FA Lays 2 and it will be complete
Thanks so much for all your help with this one. I can't wait to experiment with trying to do it in reverse, with only the other 7 sheets compared.
cheers
Hello mate
I have added ShaneX_falay1_v_faLay2() - there was 280 rows copied this time. Sound about right?
You will obviously need to do some testing and try your best to break it.
I hope it is what you were after and let me know how you get on
Purfleet
https://send.firefox.com/download/5771da73364a47d9/#oqnxhb05LmJnTrjWguqsOw
That seems spot on Purfleet
Just to confirm, I copied all the rows from FA Lays 1 and FA Lays 2 to the Confirmed Lays sheet and did a Remove Duplicates and it removed 280, so that matches up perfectly
Excellent work mate
g'day Purfleet
Sorry for the delay, I guess we are both busy enough with all the palaver going on. Hopefully things are OK there in the UK.
I guess the last thing which needs to be done is to have the 3 parts all incorporated in the one thing. You've done FA Lays 1 vs Safe Bets Lays, FA Lays 2 vs Safe Bets Lays and FA Lays 1 vs FA Lays 2 and each worked perfectly in isolation; now to see them all work as a whole.
Stay safe mate
