How to allocate rows from one tab to another based off similar text?

**bottester** · 03-18-2025, 06:33 PM

Export.xlsx

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. I've also been using the site address cells to cross verify the names too

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!

**TMS** · 03-18-2025, 06:51 PM

I think it might not be that simple, and you are going to struggle. As an example of theissue:

PHP Code:


1.  Liberty Stuart Park    34-36 Stuart Hwy

2.  Liberty Alice Springs    44a Stuart Highway

Ar you looking to match these entries?

**bottester** · 03-18-2025, 07:01 PM

Based off the data - i believe they would be different sites and thus, i'd need to manually put the coordinates for the sites that are exclusive on tab 2.

Currently, what im doing is using vlookup to isolate unmatched ones on tab 2 and going one by one to see if the names/addresses are similar/match the ones on tab 1 and highlighting exclusive/unfound sites so i can manually look up the coordinates all at once after the matched ones are done. If that makes sense - all good, if this manual process is the only plausible way but wanted to see if i could narrow down the 100 or so unmatched ones :D

**TMS** · 03-18-2025, 07:36 PM

Best I can suggest is:

C2, dragged across to H2:

Formula:

Please Login or Register  to view this content.

I2:

Formula:

Please Login or Register  to view this content.

The C2 formula will bring across the "matching" data. Note the -1 parameter: that will get the next lower entry, if not an exact match.

These formulae will spill. Then filter on column I for FALSE. It lists 190 out of 477 entries.