This is a tough one that has had me stuck for 2 days.
I have $ and Text associated with a year (Prior Year, Current Year, Next year). I want to transpose and align the Text, $, and Year without duplicating records.
Here is an example of how may data appears.
ID | PROGRAM | PROJECT | PROJECT DESC | Prior Year Text | Current Year Text | Next Year Text | BUDGET YR | AMOUNT |
16472351A | Fast Car | Materials | Funds basic research in chemistry, biology, biotechnology, and | We did this and that in 2019 | We did even more last year in 2020 | We plan to do the most in 2021 | Prior Year | 955 |
16472351A | Fast Car | Materials | Funds basic research in chemistry, biology, biotechnology, and | We did this and that in 2019 | We did even more last year in 2020 | We plan to do the most in 2021 | Current Year | 1354 |
16472351A | Fast Car | Materials | Funds basic research in chemistry, biology, biotechnology, and | We did this and that in 2019 | We did even more last year in 2020 | We plan to do the most in 2021 | Next Year | 2345 |
16472351A | Fast Car | Structure | Funds development of primary structure | Preliminary design in 2019 | Computer modeling in 2020 | Will build first prototype in 2021 | Prior Year | 345 |
16472351A | Fast Car | Structure | Funds development of primary structure | Preliminary design in 2019 | Computer modeling in 2020 | Will build first prototype in 2021 | Current Year | 2346 |
16472351A | Fast Car | Structure | Funds development of primary structure | Preliminary design in 2019 | Computer modeling in 2020 | Will build first prototype in 2021 | Next Year | 5679 |
123456A | Fast Bike | Frame | Aerodynamic super light frame | Tried this in 2019 | 3 prototypes in 2020 | Test in TOS 2021 | Prior Year | 54564 |
123456A | Fast Bike | Frame | Aerodynamic super light frame | Tried this in 2019 | 3 prototypes in 2020 | Test in TOS 2021 | Current Year | 654651 |
123456A | Fast Bike | Frame | Aerodynamic super light frame | Tried this in 2019 | 3 prototypes in 2020 | Test in TOS 2021 | Next Year | 468473 |
Here is my desired output -- unless of course, there is a more elegant solution I am not seeing.
ID | PROGRAM | PROJECT | PERIOD | COMMENTS | AMOUNT |
16472351A | Fast Car | Materials | Prior Year | We did this and that in 2019 | 955 |
16472351A | Fast Car | Materials | Current Year | We did this and that in 2020 | 1354 |
16472351A | Fast Car | Materials | Next Year | We did this and that in 2021 | 2345 |
16472351A | Fast Car | Structure | Prior Year | Preliminary design in 2019 | 345 |
16472351A | Fast Car | Structure | Current Year | Computer modeling in 2020 | 2346 |
16472351A | Fast Car | Structure | Next Year | Will build first prototype in 21 | 5679 |
12356A | Fast Bike | Frame | Prior Year | Tried this in 2019 | 54564 |
12356A | Fast Bike | Frame | Current Year | 3 Prototypes in 2020 | 654651 |
12356A | Fast Bike | Frame | Next Year | Test in TOS in 2021 | 468473 |
I've tried various flips and flops using the transpose tool and I just can't seem to get it exactly how I want it. I keep getting duplicate amounts. Help.
Thanks.
Solved! Go to Solution.
Hi @hellyars
After transpose, simply compare the strings in budget year and transposed field and you can achieve this. See the attached workflow
Cheers!!
@mtakka1 Nice. I can use this tip. I can almost get it to work, but some of the nuances of the real data are causing some problems.
This is not really solved, but I am going to post it as a solution as it works with the sample data I provided.