| I am compiling a data base on Tennis players for a college project. I have two spreadsheets with lots of relevant information, one with performance related statistics for every ATP match played over the last five years and another spreadsheet with their ranking at that moment in time, handedness and the surface from that match over the same period. I am trying to merge both spreadsheets. My problem is that although almost all the same matches are included, I am unable to match the data one-for-one. The problem being that in one spreadsheet, the names are First Name Last Name (i.e Roger Federer)and in the other, the spreadsheet has the name as Last Name, first Initial (i.e Federer R.). Also since there are many matches on the same day in the same tournaments, I can't match them up by date or tournament. I've tried assigning numbers to players to match but came unstuck. I have also tried reconstructing the names in the first spreadsheet so that Roger Federer becomes Federer R. using =MID(E3,FIND(" ",E3)+1,255)&" "&LEFT(A1,1)&"." From there I thought I could simply use a VLOOKUP to match the data based on name and date. However some players Have Double barreled first names or last names. So for my purposes, Joe-Wilfred Tsonga would need to become Tsonga J.W. or even more complicated Jabor Mohammed Ali Mutawa becomes Ali Mutawa J.M. So basically, I think it would be too hard to reconstruct the names to match but I am wondering if there is any way to match the two sheets based on the last word of one cell and the first word of another (e.g for Joe-Wilfred Tsonga, Tsonga would always be in both cells). The date can also be used since the date of the matches in both spreadsheet is obviously the same. I know this sounds quite sticky but I would greatly appreciate any ideas. Thanks in advance. |
<tbody>
</tbody>
Cross-posted here: http://stats.stackexchange.com/questions/162448/matching-data-in-excel-based-on-certain-words#
Last edited by a moderator:
