I have a specific cell in each row which contains text, which needs to be normalised.
Currently, I normalise the text by looking for a specified string with wildcards and return a value which I specify via an IF THEN and CountIF function, that looks like this:
=IF(COUNTIF(A2;("*Ginger Tea*"));"F&B";
IF(COUNTIF(A2;("*Cappuccino*"));"F&B";
IF(COUNTIF(A2;("*Sandwiches*"));"F&B";
IF(COUNTIF(A2;("*Flat White*"));"F&B";
"Other"))))
After a few weeks, it just got too much work, because new strings do popup every week because they get added by colleagues into a tool from which i pull the exports for analysis. So i am basically manually adding the new string into the cell containing the IF THEN statement and updated all cells containing that function.
There must be an easier way to it, isnt there? I was thinking of some kind of mapping table on a seperate sheet where I would add every new string allowing me to have a better overview and to also rename values where necessary. I attached sample data including a new mapping table on a seperate sheet in the hope that you have an idea on how to solve it with a mapping table.
Bookmarks