Hi there,
every time when I add a new client I would like to add three repeating words/text in the associated columns. Example (date is dd-MM-yy):
Date | Client | Amount A | Amount B |
01-01-21 | ABC | 10 | 10 |
01-01-21 | XYZ | 20 | 30 |
02-01-21 | ABC | 10 | 10 |
02-01-21 | XYZ | 30 | 30 |
03-01-21 | ABC | 10 | 10 |
03-01-21 | XYZ | 40 | 40 |
03-01-21 | EFG | 100 | 100 |
Output is supposed to look like:
Date | ABC_Amount A | ABC_Amount B | XYZ_Amount A | XYZ_Amount B |
01-01-21 | 10 | 10 | 20 | 30 |
02-01-21 | 10 | 10 | 30 | 30 |
If a new client joins the list on 03-01-21 (see first Table), say "EFG", the new client shall be added the very right end of the list with the same structure as the other clients
Date | ABC_Amount A | ABC_Amount B | XYZ_Amount A | XYZ_Amount B | EFG_Amount A | EFG_Amount B |
01-01-21 | 10 | 10 | 20 | 30 | ||
02-01-21 | 10 | 10 | 30 | 30 | ||
03-01-21 | 10 | 10 | 40 | 40 | 100 | 100 |
Could anybody help me to automatize with an endless adoption of new clients?
Thanks in advance 🙂
Julian
Solved! Go to Solution.
The transpose and crosstab tools can help achieve this. This solution should work for new additions down the line with no updates needed.
Here is how you can do it.
1. Using transpose tool convert amount columns to rows.
2. Using formula tool creating new column names.
3. Using cross tab convert back to table.
4. Using dynamic rename i using previous name to change the crosstab name.
Hope this helps 🙂