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
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
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
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
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
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
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
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
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
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
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
Have you run the macro in just the uploaded file? So just as it was on my PC?
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
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
It didn't shrink much...from 2.6Mb to 2.3Mb, but here is the DB link
https://www.dropbox.com/s/i1kfeusdc5ncdxz/New%20Results%20File.xlsm.zip?dl=0
cheers
