Index match/Join help!

[Chelseaa]

Hi all, 

i need some help with my workflow.

My input file is in the format of an excel file's pivot table - running total in Date and it has data all the way till December (this is just part of the data, from Jan to Mar)

Region	Project	1/1/2020	2/1/2020	3/1/2020
AMER	A	-1	-2	-4
AMER	B	0	-4	-10
AMER	C	-20	-25	-26
APAC	A	-15	-16	-20
APAC	B	-3	-5	-7

My desired output is:

Region	Project	Month	Achieved savings	To-be Achieved savings
AMER	A	Jan	-1	-3
AMER	A	Feb	-2	-2
AMER	A	Mar	-4	0
AMER	B	Jan	0	-10
AMER	B	Feb	-4	-6
AMER	B	Mar	-10	0
AMER	C	Jan	-20	-6
AMER	C	Feb	-25	-1
AMER	C	Mar	-26	0
APAC	A	Jan	-15	-5
APAC	A	Feb	-16	-4
APAC	A	Mar	-20	0
APAC	B	Jan	-3	-4
APAC	B	Feb	-5	-2
APAC	B	Mar	-7	0

The formula for the 'To-be achieved savings' is December's savings according to the correct region and project - the current month's savings.

eg. if we are calculating for AMER, Project B, March (and if the December's savings for this group of data = -100), to be achieved savings = -100 - (-10) = -90

this workflow might required a join tool..? but i cant figure out how to configure the workflow, please help!

Thank you in advance!

🙂 

[atcodedog05 · Accepted solution]

Hi @Chelseaa 

You have done of the work.

Here is the workflow for the task

Output:

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[Qiu · Accepted solution]

@Chelseaa 

Hope this is what you need.

[Chelseaa]

@Qiu @atcodedog05 

Thank you both, both method works 🙂

[atcodedog05]

Happy to help 🙂 @Chelseaa 

Cheers and Happy Analyzing 😀

Feel free to reach out if you face any issues 🙂