Attempt 3 - How to avoid duplicates and get totals to match when transforming and aligning

[hellyars]

HELP.  I can't seem to transform and align my data without creating numerous (dozens) of duplicates and totals that don't match the starting number.

I've tried a few times with notional data.  The help so far is greatly appreciated, but neither approach seems to translate to the real data.  So, here's a sample of the real data. YXDB attached   My goal seems very simple.  I want 1 column for text and 1 column for dollars for each period (2019,2020, 2021 B, and 2021 O).   There may not be text or dollars associated with each period.  

Unless there is a clearer solution, I am trying to structure the data along the lines of the table below.  THANKS

LINE	PROJECT	PROGRAM	PROGRAM DESCP	PERIOD	TEXT	AMMOUNT
				2019
				2020
				2021 A
				2021 B

[cplewis90 · Accepted solution]

Hello @hellyars,

Not sure if this meets your needs exactly, but figured I would give it a shot. here is a snippet of the results of the first couple records:

[DiegoParker · Accepted solution]

Hi @hellyars,

This is my approach. I basically added the year+value columns together for each year, then I did my transpose and then text to columns. Hope that helps.

Please find the workflow attached.

Best,

Diego