Using macros to find duplicates in two spreadsheets

Hi all,

I am a new poster on this forum. I tried getting help from another forum but no one there has offered any help for days now. My problem is this: I run an online baseball league, and the game I use to simulate our games has issued a new version which has updated ratings for all the baseball players. I have exported rosters from the new game and rosters from my online league to .csv files and what I want to do is find which players are duplicates in both files and line them so that I can scroll both files simultaneously and update my online league file easily. I also want the non-duplicate players to be sorted below the duplicates so I can deal with them later. Someone on the other forum posted some dynamic named range code and two macros and gave me some vague instructions on how to use the codes. I have posted the codes below with their instructions:

In both:

a) There're defined 2 dynamic named ranges:


"DataTable" as: =DESREF(INDIRECTO(DIRECCION(COINCIDIR("//Player ID";updated_rosters!$A:$A;0)+2;1;1;1;"ml_rosters"));;;CONTARA(updated_rosters!$A:$A)-CONTAR.SI(updated_rosters!$A:$A;">=//");CONTARA(INDIRECTO("updated_rosters!$"&COINCIDIR("//Player ID";updated_rosters!$A:$A;0)&":$"&COINCIDIR("//Player ID";updated_rosters!$A:$A;0)))) -----> in english: =OFFSET(INDIRECT(ADDRESS(MATCH("//Player ID",updated_rosters!$A:$A,0)+2,1,1,1,"updated_rosters")),,,COUNTA(ml_rosters!$A:$A)-COUNTIF(updated_rosters!$A:$A,">=//"),COUNTA(INDIRECT("updated_rosters!$"&MATCH("//Player ID",updated_rosters!$A:$A,0)&":$"&MATCH("//Player ID",updated_rosters!$A:$A,0))))


"DataUpdateTable" as: =DESREF(DataTable;0;25;56) -----> in english: =OFFSEF(DataTable,0,25,,56)


The changes in the now weird range definitions are due to the first and last lines like "//..." that altered the normal and usual worksheet like database structures.


b) In worksheet Sheet1 there's a procedure SortLinked in the VBA section:

-----

Please Login or Register  to view this content.

-----


c) There are 5 new columns ER:EV & ES:

ER1: title

ER2: Z (greater than below values for sorting descending because of damned row 2)

ER3 & down: =SI(ES3=0;"N";"Y") -----> in english: =IF(ES3=0,"N","Y")

ES1: title

ES3 & down: =SI.ERROR(COINCIDIR(A3;'[XXX.xlsm]Sheet1'!$A:$A;0);0) -----> in english: =IFERROR(MATCH(A3,'[XXX.xlsm]Sheet1'!$A:$A,0),0) (where XXX is the name of the other file)

ET1: title

ET3 & down: =E3&"_"&F3&"_"&K3&"_"&J3&"_"&I3&"_"&L3&"_"&N3

EU1: title

EU2: 0 (lesser than 1, for sorting ascending)

EU3 & down: =MIN(CONTAR.SI(ET$1:ET3;ET3);2) -----> in english: =MIN(COUNTIF(ET$1:ET3,ET3),2)

EV1: title

EV3 & down: "Y" if update, "" if not

In OBSL file (target) there's a module with a procedure UpdateLinked in the VBA section:

----- />

Please Login or Register  to view this content.

-----


Only once steps:

1) update your workbooks as described in a), b) & c)

2) define the named ranges in them as specified


Each time steps:

1) in both files run the procedure SortLinked

2) in target (OBSL) file run the procedure UpdateLinked


Just advise if any issue.


Regards!


PS: Remember that these files have crossed links, so if renaming happens you'd have to edit data links too.


PS2: I'm gonna take vacations of baseball (specially ML & OBSL) for a few, long, days. Good luck!

Here are my files if someone will please take a look at them and instruct me on how to use these codes. I have downloaded Spreadsheet Compare and KU Tools to try and figure this out but to no avail.

New rosters: https://www.dropbox.com/s/xdpk0bndan...ed_rosters.csv

Online league rosters: https://www.dropbox.com/s/8arhr605w4...sl_rosters.csv

Thanks,

Stacy

Hi Stacy,

Firstly, welcome to the forum

I dunno if I am speaking for others on the forum, but I took one look at your **lengthy** question, attempted to read through it and was left completely baffled. I think this is why you are getting such minimal response on your post....... It's way, way to complex! and BTW: that opening and utterly complex formula does not help to ignite any willingness, just a mild case of fear!!! LOL

Anyways, back to the point. I think you'd get much more help if you did not present your ENTIRE project to the forum, but rather break it up into smaller pieces and let us help you with one challenge at a time. We do not know much about your needs and the comparison/analysis template you seem to want to design, so giving us the entire process just muddles with our minds -- and makes it appear as too much work. Many of us here are offering our time and effort freely and the forum is not intended as a platform for large project design.

I think you'll find it much more beneficial to present us with a single scenario and get some solution on that. Then you can integrate it into your file and if needed, ask a second question, get help and integrate that; until you have a working model that suits you.

Hi Stacy,

The Pivot Table feature in Excel is a powerful tool that can take raw data and summarize, organize and compare info.
In the sample file attached you will see a mock-up (with tiny amount of dummy data) in illustration of what I can suggest to compare players and find duplicates between the Original data and the Updated data. Using Pivots will certainly be the easiest and most flexible way to find duplicates and determine if they are withing the same .csv or duplicated between the two .csv files without the need for macros, formulas or any manual process.

I'm not sure what ultimately needs to be done with these comparisons, but if it is simply to collect statistics, or find inconsistencies, then this is the way to go.

In the mock-up the first sheet is the original data
In the second sheet is based on the original with new updated names as well as deleted names from the originals
The third sheet is a combination of both into one sheet
The fourth sheet is the summary pivot table showing comparisons and identifying duplicates

I'm hoping this will provide an idea of how you can compare these two lists with the least amount of effort on your part.
Let me know what you think and if this is a viable option.

Understood.

Instead of you doing this manually...(sorting, copying/pasting), we can script a macro to do this automatically.
In stead of fussing with sort orders and syncorized scrolling, we can have the macro locate the player using some unique code between the files and then update the stats based on a find. The only important condition is that we need to determine a way to locate the player uniquely in the obsl_roster based on parameters in the updated_roster. Is there a way to do this? If we combine PlayerID and Name and Birthdate (for example), will it create an absolute unique code that can be located in the obsl_roster? If it can, then the macro can take the stats in Z - EQ and replace it with the updated stats.

Is this a viable solution? If so...what fields will uniquely identify a player between the two files?
TX

Great. I'll start setting up a macro to deal with this.
BTW: I'll include NationalityID too...just in case you get a same name and birthday. Better safe than sorry

Hi Stacy,

Here is a template containing the macro.
When you click the button it will do the following:

1. It will prompt you for the two csv files. I assume that they are called opsl_rosters and updated_rosters. This is important as the code will be referring to these names.
2. The code will start to run and process the opsl_rosters records, updating the stats with those from the updated_rosters. I have locked the screens so you will not see any movement. I did add a counter on Excel's status bar (bottom left) so you can see progress. The code will probably run for a few minutes due to the >10,000 records.
3. Once the code is run, you will see that most codes in the Code column on the opsl_rosters sheet are blue. These are the records that were updated. The ones that are still balck text was not updated due to no matches in the updated_rosters sheet.

Please run the code and see if you require any changes/modifications...

Hi,

The records from updated which have no matches with those in obsl are now highlighted in RED.

OK...here it is...

Excellent...glad its working

As long as the structure of the two cvs's are similar it will function fine. The amount of records in either does not matter...there is no need to change ranges. The macro calculates the height (or amount of rows) automatically and compensates for different amounts of rows.

The file names (and sheet names) MUST be the same as what we have currently used. I have not written in any generalization for these (although it is possible to generalize them).

TX, but unfortunately we don't use dollars here, so it won't help! LOL....

I'll have a look...
To confirm;
1. If the combined data of E and F columns is the same in both sheets?
2. Take ONLY the A column data of updated or take the entire column?
3. Must it be added to the bottom (appended to) the obsl sheet (in blue colour)?

Another thing (I have just opened those files again)...
If the combined columns of E and F is to create a unique value to compare... I'd recommend to add one or two other fields to ensure uniqueness. In your volume of data, just Team Name and Last Name might have several duplicates....???

OK... I'll send the macro soon....

Hi,

See if this code provides the expected results...

Please Login or Register  to view this content.

Just check if there is no leading or trailing spaces in the sheet name.

I tried putting .csv at the end of both files and I still get the same error.

Hi Stacy,

That error is informing that the macro does not find a sheet called "obsl_rosters" in the current workbook in which you are running the code. Either the sheet is not existing or there is a spelling error in the name. I have run is successfully on my side each time. Maybe what you can do is first run the code that compares the two sheets and marks the records in blue and red. Once that code has run and the two sheets have been imported and processed, then run the macro in question. Actually the two can really be merged as this macro giving you the error simply is a continuation of the previous one.

Let me know what happens if you run them one after the other: First the macro called: Update_obsl and then the one called: GetTeamPlayerMatches

Good to know

Well, I'm back. I tried using the macro in Stacy.xlsm in post #17 on the same files and I get this error:

Run-time Error '13': Type Mismatch

And when I click Debug, this is the line that it says has a fault in it:

upd.Range(upd.Cells(rC.Value, "Z"), upd.Cells(rC.Value, "EQ")).Copy rC.Offset(0, 26)

Any idea what the problem is? I haven't messed with this at all. Here are links to the same files:

https://www.dropbox.com/s/4ocl4ac9ni...sl_rosters.csv

https://www.dropbox.com/s/7v3y05iuuk...ed_rosters.csv

Thanks.

Hi,

The structure of your obsl_roster has changed.

The macro does not take compensate for structural changes in the file. You will see that in the current obsl_roster there is a list of items above the actual player stats list. This is causing the error. See image 1: 1.jpg

Either you must delete this data (and the row just below the column headings) or move this this data to the bottom of the file like it was in the original csv's that you posted. See image 2 which illustrated the data cleanup: 2.jpg

Unfortunately the macro does not take this structural change into account. It might be best to check the file structures before you run the macro, and "clean" the lists if needed. Basically the macro expects the list to start in cell A1 and that the list does not have any blank rows. Also, I might mention that the names of the csv's must remain the same as they too are hard-coded into the macro. If you clean up the obsl_roster, the macro will run fine again.

No probs... Glad I could assist again.

sborah, I need to poing out 3 things to you...

1. for each new question, start a new thread
2. use code tags when posting code
3. upload workbooks to the forum, not all members are able - or willing - to use file-hostong sites.

Thanks