March 3, 2020
Hi
Thanks so much for your help.
Any reason for a binary versus an xlsm file? Do you find it to be more efficient or faster that way? I'd not seen one before.
Can I assume that this For Each ws In ThisWorkbook.Worksheets addresses which sheets are to be compared? Unfortunately only the 3 I gave you were to fit the bill for this comparison. They are what is called Lay bets; it basically means you are wagering that something doesn't win, rather than betting it will win. The other sheets are the reverse. So I was planning on seeing once this one worked on the 3 sheets, then writing a new one to address the other 6 sheets.
I spotted this at another forum, where they have used the above code for all worksheets, but then excluded 3 of them by using this
For Each ws In ThisWorkbook.Worksheets
Select Case exclude Case "Summary", "Employees", "Project Rules"
'do nothing
Case Else
'do your formatting
End Select
Next ws
Could something like this be used to exclude the other sheets on this particular occasion? If so, that would be great as I could simply exclude the ones which are Back selections for this one and then exclude the ones which are Lay selections for the next macro. I can see how to do the Case part but just wasn't sure what would go in Case Else. Just not 100% sure how to incorporate this to exclude the required sheets. On checking it further, it would probably be this sort of statement in the Sub SetUp() just after this line For Each ws In ThisWorkbook.Worksheets
Case "Safe Bets Lay", "PP1", "PP2", "FA Racing", "FA Racing 2", "FA Racing 3", "Debut Destroyer"
Case Else NOT SURE WHAT WOULD GO HERE
End Select
Am I on the right track with this?
Another anomaly is that all rows are copied, including the header rows from each sheet, so there are 9 header rows through the Confirmed Lays sheet. I tried changing the range from Range("A1:W" & currentWSLastRow) to A2, but it didn't like that one. There will be a way, I'm just not spotting it yet. The RemoveDuplicates line should be handling it, but for some reason, it is not working at all, with duplicates all through the Confirmed Lays sheet.
I also removed the autofit rows and columns so it doesn't adjust anything and removed the colours from the tabs.
To avoid any of the confusion with some of the slightly different columns, could the range not have column names as exclusions? Race Value, Jockey Dis Place SR & Jockey Crs Place SR would be the only exclusions, thus making all the data the same size.
On thinking about it, just as with the Case example above to exclude particular worksheets, could not Select Case be used to exclude particular column names from the selection procedure?
Thanks so much for all your effort. Just trying to fine tune it now so it works as I need, but it seems not far off.
Let me know your views on any of the above
Cheers
Trusted Members
December 20, 2019
Any reason for a binary versus an xlsm file? Do you find it to be more efficient or faster that way? I'd not seen one before.
From what i have read xlsb files are more efficient especially for larger files and they are also significantly smaller. I was seeing if we could get the file under the 1gb limit to upload. (just found a great new way of sharing larger documents, and they auto expire - https://send.firefox.com/ - you can upload up to 2.5gb if you have a firefox account)
Can I assume that this For Each ws In ThisWorkbook.Worksheets addresses which sheets are to be compared? Unfortunately only the 3 I gave you were to fit the bill for this comparison. They are what is called Lay bets; it basically means you are wagering that something doesn't win, rather than betting it will win. The other sheets are the reverse. So I was planning on seeing once this one worked on the 3 sheets, then writing a new one to address the other 6 sheets.
I spotted this at another forum, where they have used the above code for all worksheets, but then excluded 3 of them by using this
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Select Case exclude Case "Summary", "Employees", "Project Rules"
'do nothing
Case Else
'do your formatting
End Select
Next ws
Could something like this be used to exclude the other sheets on this particular occasion? If so, that would be great as I could simply exclude the ones
which are Back selections for this one and then exclude the ones which are Lay selections for the next macro. I can see how to do the Case part but just
wasn't sure what would go in Case Else. Just not 100% sure how to incorporate this to exclude the required sheets.
On checking it further, it would probably be this sort of statement in the Sub SetUp() just after this line For Each ws In ThisWorkbook.Worksheets
Select Case ws.CodeName (not 100% about what goes next to Select case, but took a guess)
Case "Safe Bets Lay", "PP1", "PP2", "FA Racing", "FA Racing 2", "FA Racing 3", "Debut Destroyer"
Case Else NOT SURE WHAT WOULD GO HERE
End Select
Am I on the right track with this?
I have never really had the need to use case, but from what i know it looks like you are on the right track
Another anomaly is that all rows are copied, including the header rows from each sheet, so there are 9 header rows through the Confirmed Lays sheet. I tried changing the range from Range("A1:W" & currentWSLastRow) to A2, but it didn't like that one. There will be a way, I'm just not spotting it yet. The RemoveDuplicates line should be handling it, but for some reason, it is not working at all, with duplicates all through the Confirmed Lays sheet.
I also removed the autofit rows and columns so it doesn't adjust anything and removed the colours from the tabs.
To make the filter more efficient I thought it was better to leave the header rows in and remove with the remove duplicates - when I run it, it only left in the header from the worksheet that was different to the rest and maybe one extra at the top but again this is a fairly easy fix. Your call on the autofits but hidden data can cause unexpected results - it’s not doing anything now as it has been saved after unhiding but if you were to hide rows/columns again this would make sure they are visible - it is more of a best practice part than anything else.
Tabs were only coloured to make sure the macro was running on all sheets
To avoid any of the confusion with some of the slightly different columns, could the range not have column names as exclusions? Race Value, Jockey Dis Place SR & Jockey Crs Place SR would be the only exclusions, thus making all the data the same size.
On thinking about it, just as with the Case example above to exclude particular worksheets, could not Select Case be used to exclude particular column names from the selection procedure?
Autofilter needs columns to be exactly the same in my experience - feel free to test but i would make them the same with any extra columns at the end. If that column really HAD to be in H, then you could get the Macro to move it to the end, do the filter and then move it back for the file report. Again, this is up to you and your testing.
For the final results, if case works as you expect it to you would be left with one sub for Lays (3 sheets) and one sub for bets (7 sheets)?
Off to bed now to read up on VBA Case statements
Purfleet
March 3, 2020
To make the filter more efficient I thought it was better to leave the header rows in and remove with the remove duplicates
That was actually the point I was making - no duplicates are removed. Even if I run the original macro in the .xlsb file I downloaded, there are duplicates all through the sheet, so the FinishUp macro is not doing what it should. Any thoughts on what is stopping it from doing what it should?
cheers
Trusted Members
December 20, 2019
March 3, 2020
https://send.firefox.com/downl.....z_iY_FScCg
Highlighted just a handful, but there are absolutely loads of them
cheers
Trusted Members
December 20, 2019
Okay it makes more sense now. When i said unique I was comparing all columns so all columns must be a duplicate, but your data changes from column P (sometimes column C as well)
For example, Rows 5 and 7 - Blue Skyline in the 12:30 at newmarket
All of these are the same (a to o) - (picture 1)
But in pitcure 2 - these are different and therefore not unique in fact it looks like the data has move on one column in the second row which again, might be due to some inconsistencies in the columns headings in the original worksheet
This isn’t a huge problem if you only need to compare the first few columns as you can just change this line of code to and remove the columns you don’t want to compare for example if you only wanted to compare a to o (o is the 15th column) so delete all numbers after 15.
Range("a:x").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24), Header:=xlYes
I really would recommend looking at your sheets for inconsistencies as it will make anything you do going forward so much easier if it is all the same
Purfleet
March 3, 2020
Thanks for the reply Purfleet. In the end, the only columns really required for a match are A, B & H. If those 3 match, then it is a dupe and those 3 columns are nothing more than Date, Time and Name, so there really can't be any inconsistencies.
The downside is changing the Array to just be 1, 2, 8 also made no difference. In fact, even trying to do a manual Remove Duplicates also had no effect, so really not sure what is going on. I even selected A, B and H individually and manually formatted them to make sure the data is the same. I also tried selecting each column, then copy and paste special and paste the data in as values, but not a single duplicate is found.
I am clearly looking at duplicates, yet Excel does not find them.
Ever experienced this before?
cheers
The following users say thank you to Shane O'Sullivan for this useful post:
PurfleetTrusted Members
December 20, 2019
I just recorded a Macro to remove duplicates on one of the older versions we were playing with and it created the following code (i would remove the row numbers in the range to make sure all future rows are in included in subsequent runs)
Columns("A:X").Select
ActiveSheet.Range("$A$1:$X$46").RemoveDuplicates Columns:=Array(1, 2, 8), Header:=xlYes
This reduced the 'confirmed lays sheet' from 46 rows to 23.
As a suggestion, comment out or remove the remove duplicates line of code in your macro, run the macro through and then record a macro to remove the duplicates (i assume it is the same in Excel for Mac), hopefully it should have removed some of the rows and you will then have the code you need.
>Click record Macro
>Select all columns on worksheet
>Click Data tab & click remove duplicates
>Un-tick all the columns
>Tick Date, Time & Horse then ok
Don't forget that remove duplicates will only keep the first duplicate in a data-set, so you might need to sort before removing dups if you want a different duplicate.
Purfleet
March 3, 2020
Howdy Purfleet
What a struggle this has been and is still ongoing. The bottom line is the VBA compares all the sheets and it has proven very difficult to try and exclude the ones which are unnecessary.
The only 3 sheets which are required to be compared are Safe Bets Lay (might be SBLay in the one you had), FA Lays 1 & FA Lays 2. I think it will be best to try and target only those 3 sheets.
I have tried this in the SetUp()
Sub SetUp()
Dim sheetsArray As Sheets
Set sheetsArray = ActiveWorkbook.Sheets(Array("Safe Bets Lay", "FA Lays 1", "FA Lays 2"))
Dim sheetObject As Worksheet
' change value of range 'a1' on each sheet from sheetsArray
For Each sheetObject In sheetsArray
'Do something
ws.Tab.Color = xlNone
If ws.FilterMode = True Then ws.ShowAllData
If ws.AutoFilterMode = True Then ws.AutoFilterMode = False
Next sheetObject
Worksheets.Add.Name = "Criteria"
Worksheets("Confirmed Lays").Range("1:1").Copy Worksheets("Criteria").Range("1:1") End Sub
I think the CritWS all over the code still has it addressing all sheets. Should maybe each reference of CritWS be SheetsArray or something which, since that is now defined as only those 3 sheets.
cheers mate. Still unsure how to get it to only work on the 3 sheets
Ta
Trusted Members
December 20, 2019
Hi shane
Okay mate, better to start again i thnik.
Can you resent the whoole file as is and we can work through it - i think we have the correct idea, it is just getting to work for your data.
Have you managed to tidy up the 3 sheets that need comparing? So all coulms are the same?
About to try and get back to the UK in the next few hours, so will check out over the weekend as we have no football or cricket to watch!
March 3, 2020
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/downl.....bqlQCP4aSA 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
Trusted Members
December 20, 2019
March 3, 2020
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
Trusted Members
December 20, 2019
Trusted Members
December 20, 2019
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
March 3, 2020
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
Trusted Members
December 20, 2019
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
1 Guest(s)