How to replace comma separated values to comma separated ids in the same/individual cell in excel?

1
votes

I have a table where Products are mapped with ingredients and another table where Ingredients are mapped with IDs like this below :

enter image description here

Below is what I need :

enter image description here

Here I want to replace the comma separated ingredients values in "Data Table" with their IDs from "Master Table".

I checked out Replace Function to accomplish this task but couldn't manage to do it somehow.

=REPLACE(C2,1,LEN(C2),..

Here C2 i.e. Ingredient column in "Data Table" doesn't have single value to replace it easily, they are available in combination for which replace statement is not the correct solution although the same can be handled programmatically but I want to do it in excel way.

It would be very grateful if someone can guide me with solution to this problem.

Thanks in advance.

2
excelexcel-formula
To be very honest, I am new to excel considering such logic, I can do this by some javascript and C# program but I want to it through excel. I tried writing replace statement which needed some more additions. – KeVee
My guess is that it will be best to solve via VBA so the [excel-vba] tag was probably appropriate (but there are some pretty clever people watching the [excel-formulas] tag who will possibly come up with a formula solution!), but questions are always better received if you at least show you have attempted to make a start on the solution. – YowE3K
What have you tried? Where did you run into problems. For formulas, you will need to have multiple nesting levels or split your data using helper columns. VBA would be simplest. Please read the HELP pages for information as to What topics can I ask about here?, How to Ask a Good Question, and How to create a Minimal, Complete, and Verifiable example – Ron Rosenfeld

2 Answers

1
votes

This is not a direct solution nor a VBA solution but through a manual action and 2 simple formula's, this is possible.

I used a combination of Data>Text to Columns, VLOOKUP and Concatenate functions to achieve your result table.

Step 1.: Delimit your data using Text To Column.

Step 2.: Add VLOOKUP Functions for each ingredients.

Step 3. Use the Concatenate function to bring all the VLOOKUPS together.

You can find my solution using your example here in a Google sheet:

1
votes

The only thing I can come up with is a nested SUBSTITUTE function like this one:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1,D1,E1),D2,E2),D3,E3),D4,E4)

Here your Ingredients are in Column D and their respective IDs in Column E (i.e. your Master table in Sheet 2).

As you probably have many ingredients, you can use CONCATENATE to create the correct formula. Assuming your have your recipe in column E, your ingredients in column F and the corresponding ingredient IDs in column G:

  • Write SUBSTITUTE(E2,F2,G2) into D1 (where E2, corresponds to the first entry in the Ingredients column in your data table, F2 corresponds to your first ingredient in your Master table, and G2 corresponds to the related first Ingredient ID in the Master table)
  • Write SUBSTITUTEinto A2
  • Write =ADDRESS(ROW(F3),COLUMN(F3),4) into B2 (where F3 references your second ingredient in your Master table)
  • Write =ADDRESS(ROW(G3),COLUMN(G3),4) into C2 (where G3 references the corresponding ID to the second ingredient in your Master table)
  • Write =CONCATENATE(A2,"(",D1,",",B2,",",C2,")") into D2
  • Drag A2:D2 down as far as necessary

You now have the necessary formula. Copy, paste the last entry, selecting Paste Values and set a = in front.

enter image description here