Transposing data with multiple columns

[shravanvijayaprasad]

I know this question has been asked and answered  a few times but I am struggling with it anyway. 

I am trying to take the below set up:

Fund	Fund Name	Period 1	Period 2	Period 1 Exchange	Period 2 Exchange
Fund 1	Fund Name 1	100	150	0.25	0.24
Fund 2	Fund Name 2	200	250	0.87	0.86

and change it to this:

Fund	Fund Name	Period	Exchange	Period Value	Exchange value
Fund 1	Fund Name 1	Period 1	Period 1 Exchange	100	0.25
Fund 1	Fund Name 1	Period 2	Period 2 Exchange	150	0.24
Fund 2	Fund Name 2	Period 1	Period 1 Exchange	200	0.87
Fund 2	Fund Name 2	Period 2	Period 2 Exchange	250	0.86

Any help would be appreciated.

P.S - I tried transposing twice but it creates Period 1 with Period 1 Exchange and Period 2 Exchange for fund 1

Thanks in advance!!

[Jonathan-Sherman · Accepted solution]

Hi @shravanvijayaprasad,

This should achieve what you're looking to do. I also used two transpose tools but then filtered after to remove rows not required.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[shravanvijayaprasad]

This solves the issue for this mock data and would work but I'm wondering if there is a different way to do this (waiting and hoping there is different answers. Also, interesting use of Regex, I haven't used Regex so much, could you explain what exactly is happening in the formula part?

[shravanvijayaprasad]

@Jonathan-Sherman Thank you so much for the answer. Just as a curious follow up, what if the columns aren't named as Period 1 and Period 1 exchange and is instead renamed as Period 1 and Exchange One or something like that?

[Jonathan-Sherman]

@shravanvijayaprasad the regex_replace function is extracting the number from the cell and overwriting the cell with just that number. For your follow up question, if they aren't named Period 1 and Period 1 exchange but period 1 and period one exchange i'd change the formula to something along the lines of:

CONTAINS([column 1], 1) AND CONTAINS([Column 2], "one")

OR CONTAINS([column 1], 2) AND CONTAINS([Column 2], "two")

If this solves your question please mark the answer as correct, if not let me know!

Regards,

Jonathan