This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
After using find\replace function to remove conjunction words [and, of, …] in [Column1] in Table A, I want to use [Column1] values in Table A to identify all records with matching words in Table B [Description] column and place results of all partially or fully matching records ID’s (from Table B) in [Result] column in Table A as presented.
There are likely a few different methods for solving this one, and you'll surely need to tweak to make it work with your "real" data, but here is a possible solution...
The key for this method is identifying all the "lookup words" from Tbl A, and getting them into rows (use a Parse tool to split by whatever delimiter you decide to use - I chose a | pipe delimiter)... and then append all those values to Tbl B. Then, for each combo, check to see if the Tbl A value is contained in Tbl B value. At the end, a series of Group Bys + Concatenate will give you the Result field you're looking for.
This will be less efficient if you end up with a scenario where you have a LOT of records in Tbl A, because appending a lot of records to every record in a Tbl will create... a lot a lot of records. So if that's the case, you might instead look at splitting every "word" in Tbl B as well, and then doing a simple join between the two data sets. Then, you can group by the Record IDs from both Tbl A and Tbl B that match from the Join output, and then Group by & concatenate to get your final result data set.
Hopefully that makes sense/points you in the right direction! If you need a little more guidance, let me know 🙂