Adding Text to column

Question

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

ClientAmount AAmount B01-01-21 ABC101001-01-21XYZ203002-01-21ABC101002-01-21XYZ303003-01-21ABC101003-01-21XYZ404003-01-21EFG100100

Output is supposed to look like:

DateABC_Amount AABC_Amount BXYZ_Amount AXYZ_Amount B01-01-211010203002-01-2110103030

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

DateABC_Amount AABC_Amount BXYZ_Amount AXYZ_Amount BEFG_Amount AEFG_Amount B01-01-2110102030  02-01-2110103030  03-01-2110104040100100

Could anybody help me to automatize with an endless adoption of new clients?

Thanks in advance 🙂

Julian

Luke_C · Accepted Answer

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.

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

atcodedog05 · Accepted Answer

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 🙂