Combining and Summing Rows Based on a LookupTable

[Nathan_Boynton]

Hi Everyone,

I am trying to combine particular rows of data and change data labels based on a LookupTable.

My input looks like this:

My lookup table looks like this:

From my input, I want to be able to change the "Juice" label and sum the rows of the particular juices that have the same "Blended" label from the lookup table. For the values that are not included in the lookup table, I would like to keep the same.

My desired outcome would look like this:

To be more clear on how the rows should be combined, please see the reference calculation below:

I have attached an Excel file with the input, output, and sample calculation. I would really appreciate any help with this! Thank you all so much! 

Best Regards,

Nathan

[Jean-Balteryx · Accepted solution]

Hi @Nathan_Boynton ,

Here is a workflow producing your desired output.

Basically, it replaces fruits by blend then group by blend and year.

[KarolinaRoza]

hi @Nathan_Boynton 

You can apply:

- Find Replay tool to change data in Juice column

- then Summary tool to get desired sum.

I also attach you my workflow.

Hope it helps.

Regards,

Karolina