Find and Replace With Multiple keyword Match

[Pratik]

Hi. I need help with following. I have two files. One file with "item description" and another file with bunch of "Nouns". I needed to solve the following:

Question 1. I want to be able to look into the "item description" and see if any of the words from the "Noun"  exist there. If they exist I want the matching "Nouns" to be appended right next to the "Item Description".

Question 2. If multiple words from the "Noun" field exist in the "Item Description" then I want them to be concatenated together separated by Comma (,) .

I was able to solve question 1 by using Find Replace. But, I need help with question no 2.

For Example:

	Item Description		Noun
	50mL Centrifuge Tubes, Sterile, Clear, Graduated, 25/Bag, 500/Case		Case
	15mL Centrifuge Tubes, Sterile, Clear, Graduated, 25/Bag, 500/Case		Tube
	Micro-Adson Forceps-Fenestrated Handle/Serrated/Straight/12cm		Bag
			Handle
			Forceps
	Desired Output:
	Item Description	Noun
	50mL Centrifuge Tubes, Sterile, Clear, Graduated, 25/Bag, 500/Case	CASE,TUBE,BAG
	15mL Centrifuge Tubes, Sterile, Clear, Graduated, 25/Bag, 500/Case	CASE,TUBE,BAG
	Micro-Adson Forceps-Fenestrated Handle/Serrated/Straight/12cm	HANDLE,FORCEPS

Thanks in advance.

[binuacs · Accepted solution]

@Pratik one way of doing this with the help of batch macro

[KGT]

The way that I would do this is to separate all words to a new line and then do your Find Replace, you can then group again after that.

• RecordID
• Text to columns (Delimiter = \s)
• Find/Replace
• Summarise.

[Ritesh_Parmar]

if u have only these 2 columns, than append & use Formula  : Contains([Item Description], [Noun])       filter out & Summarize - Group by item Desc & Concatenate Noun

[flying008]

Hi, @Pratik 

FYI.

[Pratik]

Thanks. It worked!