Forum Discussion
How to allocate rows from one tab to another based off similar text?
Hi,
Curious to know if there's a easier way to automate this process or if the only method is manual
Basically, as seen in my attached sheet, i have tab 1 with a "masterlist" of brand petrol stations and their allocated coordinates. On tab 2, i have a list of special brand stations that i extracted from another source. I want to allocate each of the sites in 2 to 1 so they get their corresponding coordinates attached to them.
Some of the names from 2 are the same as 1 so finding a match via vlookup is fine. However, some names have some differences e.g "Site Andy" in tab 2 is called "Site Andy Unmanned" and im assuming fuzzylookup is needed but unsure on how to allocate tab 1's coordinates to them. Additionally, i'm aware that some names on 2 might not exist on 1 so manual data entry/googling is needed which is fine.
Just looking how to get the quickest way to allocate the corresponding coordinates to same/differently named but same sites and highlight the non-existing ones so i can manually find the coordinates. Thanks!
1 Reply
I ran 3 different XLOOKUPs on your data.
- Standard XLOOKUP - 287 returns
- XLOOKUP using Regex match mode (This looks for the text anywhere within the string) - 300 returns
- XLOOKUP w/Wildcard match - 300 returns
If you're using a fully updated Excel 365, you'll have access to the 2nd option. I've included all formulas in the attached. I hesitate to try a partial matching without Regex because then it becomes a matter of how much of the text do we use to match and it can result in false matches.
