I have two lists of property names. Sometimes a name in List A has extra characters; sometimes List B has the extra characters. I want to pull associated data from a column in the tab with List A and add it to the tab with List B, by property name.

A few examples are:

List A List B
AMLI Riverside AMLI on Riverside
Adam Hats Downtown Lofts Adam Hats Lofts
Atlantic at Kessler Park Atlantic at Kessler Park, The

My current thought for a solution is to split by word in List A and add wildcard operators inside Query with the Like operator in the Where clause. I think I would need to do so before, after, between each substring. I haven't tested it yet, but something like this:

=query(A2:A,"Select _ where _ like '%AMLI%Riverside%'")

And then would need to also build in the string splitting, presumably with SPLIT around " ".

Before I go down this rabbit hole - am I missing a better solution? There are ~650 properties in List A and ~2000 properties in List B, so would prefer not to check manually.

  • 1
    Welcome to Web Applications Stack Exchange. This is not a trivial problem, and you may need fuzzy matching to make it work. Choose Add-ons > Get add-ons and search for fuzzy to review possible solutions. Aug 5 '21 at 19:02
  • HI and welcome. I agree 100% with @doubleunary - this is not trivial. I can imagine a user-developed script based on the javascript method indexOf. But it is moot whether the development time could be justified versus buying/using a fuzzy logic add-on.
    – Tedinoz
    Aug 7 '21 at 9:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.