Log In
Register
Home
March 3, 2020
Offline
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
Trusted Members
December 20, 2019
Offline
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
March 3, 2020
Offline
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
Trusted Members
December 20, 2019
Offline
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
March 3, 2020
Offline
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
Trusted Members
December 20, 2019
Offline
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
March 3, 2020
Offline
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
March 3, 2020
Offline
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
Trusted Members
December 20, 2019
Offline
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
March 3, 2020
Offline
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
March 3, 2020
Offline
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
Trusted Members
December 20, 2019
Offline
Have you run the macro in just the uploaded file? So just as it was on my PC?
March 3, 2020
Offline
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
Trusted Members
December 20, 2019
Offline
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
March 3, 2020
Offline
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
Trusted Members
December 20, 2019
Offline
Okay will look tonight when I get home as I can't download at work
Trusted Members
December 20, 2019
Offline
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
March 3, 2020
Offline
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
Trusted Members
December 20, 2019
Offline
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
Trusted Members
December 20, 2019
Offline
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
1 Guest(s)
