Run same process for multiple sheets- Looking for Macro Experts

[ropra]

Dear All,

So here is my situation.

Please find the screenshots above. 

I have to find "Words to Find" in "Description" and append words accordingly. 

Now the ideal case would be if I will be able to find all the words in the description at once. I use find and replace tool. What it does is, append the first word it finds and append. So I have to use find and replace multiple times. You can refer to the attached workbook. Even in this it would be great if I can use macro and repeat the process for each excel sheet without having to do it manually each time. Any other way is also appreciated. Please don't suggest formula/regex/etc. as the number of tabs is very high 50+ and will increase, so these solutions are not 

[lmorrell]

Hi @ropra 

Workflow is attached. 

At great risk of underthinking your dilemma, if the output of your word + replacement sheets are the same, would it be possible to only input the sheets that meet the data source template via a dynamic input? By following the first few steps of this excellent guide I was able to load a dataset as below:

From there, we can find any instance of "Words to Find" within the cell, and replace with "Append" into a new column to give the desired output. 

Hope I understood the gist of your problem, and that this can help!

[ropra]

Hey @Imorell

I want to thank you for replying so promptly. Feels so good to be a part of a community which takes care of everyone.

I was at fault of not explaining the full situation.

So these words are to be found in a very long description that is why I would like to append and not replace. I know I can replace the words with some witty words and then use a formula to see if they were added or not but it would be much easier if I got a way to use a macro in this.

I hope you understand my situation.

Regards,

Rohit

[lmorrell · Accepted solution]

Hi Rohit, 

In turn - thank you for your contributions to the community! Always happy to help good people dealing with interesting challenges. 

Solution to append fields is attached. 

Yes - given your context this makes a lot of sense, as the aim is to understand the changes that have occurred to an original message rather than to make the changes. I've extended the logic of reading in all the combinations of Find and Replace words via the Dynamic Input Tool, and have connected it to a Batch macro that Appends and Unions back to the main dataset, cycling through each combination of Find and Replace pair. This macro has a picture of a Fruit Salad in honour of your test data. 

Based on the output, and the format you require, I have done example continuations via Crosstab or Transpose in order to make everything a bit more readable. Please have a look, but I feel that with a bit of tweaking, it will be able to meet the situation. 

Hope this helps!