Transpose identical field columns

jamiep004

Hi, below is an example of information that I have in a excel sheet:

Name    |  Date     |  CC Statement | CC Repayment |  Balance |  Date     |CC Statement |  CC Repayment  |   Balance   |   

John      | Jun-18  |        600            |         -600          |        0      |  Jul-18   |      320           |               -             |      320       |     

Mary      | Jun-18  |        950            |         -400          |     550     |   Jul-18  |                       |                              |     550        |            

I would like this information transposed in a way (as shown below for example) that allows me to create a pivot table in excel:

Name   |   Date    |     Category        |     Value    |

John     |  Jun-18 |   CC Statement  |     600       |

John     |  Jun-18 |  CC Repayment |     -600      |

John     |  Jul-18  |   CC Statement  |     320       | 

Mary     |  Jun-18 |  CC Statement   |      950      |

Mary     |  Jun-18 |  CC Repayment |     -400      |

The columns CC Statement and CC Repayment repeat for each month of the year going across. In my actual worksheet I have more 'categories' but for the purpose of ease I limited the example to 2 columns. When trying to transpose the information above Alteryx renames duplicates (CC Statement, CC Statement 2, etc). I accomplished my goal but placing the data for each month in separate sheets. But I would like to know if I can get the result I want using one sheet as above. 

Thanks.

adamorse

I've attached a workflow that might give you some ideas. The basic outline is that you first transpose *everything*. Then you can extract the digit that the transpose adds (2,3,...) and use that as an index to join the data together into a single table. As you can see in the workflow output below, the "Right_Index" is calculated by pulling the digit the transpose adds and is used to associate the correct dates and payments together.

("test.csv" is just an input based on the input table in your post that i used to test the workflow)

handle multiple columns.yxmd

test.csv