Adding Text to column

[julianhoetzel]

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

[Luke_C · Accepted solution]

Hi @julianhoetzel 

The transpose and crosstab tools can help achieve this. This solution should work for new additions down the line with no updates needed.

1. First I transpose the data to get all the amounts in one column
2. Then I concatenate the client and amount name
3. Then cross-tab that data back to have the clients/amounts as columns.

[atcodedog05 · Accepted solution]

Hi @julianhoetzel 

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 🙂