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 · Accepted solution]

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)

[jamiep004]

Thanks a million! It worked.

[jamiep004]

Hi there, I need further help on the workflow provided. The transpose tool is adds single digits up to 9. Then any repeat of identical columns after that gives 9_2. The right index is only pulling the 2 whereas I will need the entire 9_2 extracted. Is this possible?

Thanks.

[adamorse · Accepted solution]

Maybe try replacing the formula that creates the "Index" variable (first in the Formula tool) with something like

If Contains([Name], "_") Then Right([Name],Length([Name])-FindString([Name], "_")+1) Else Right([Name], 1) EndIf

Let me know if you need any other pointers on this

[jamiep004]

Thanks for your reply. There are three expressions in the formula tool. I'm not sure which one I should replace. Can you guide further please?

[jamiep004]

It have figured it out. Thanks for your help!

[asteryx]

@adamorseFound this while trying to solve a similar problem. Thanks for your input. I had to do a lot of adjustments, but your approach was key.