Tranposing multiple columns with String and Numeric Values

[jackdaniels]

I have been trying to reshape the following using Transpose / Formula, but I can't do it as I am mixing strings and numeric values.

From this

Dimension  1	Dimension 2	Category 1	Amount 1	Category 2	Amount 2	Category 3	Amount 3
Random	Word	Export	30	HR	50	Cash	500
Random	Test	Import	40	Finance	60
Something	Else	Tax	50

To This

Dimension  1	Dimension 2	Category	Amount
Random	Word	Export	30
Random	Word	HR	50
Random	Word	Cash	500
Random	Test	Import	40
Random	Test	Finance	60
Something	Else	Tax	50

How can I do this?

[tcroberts · Accepted solution]

Hi!

I've created a sample workflow for you here:

Essentially I transpose using Dim1 and Dim2 as keys, then a MultiRow Formula helps me to create the Category field you're looking for. I then remove all fields where [Value]=[Category], and get your desired output.

Let me know if this helps,

Cheers!

[jackdaniels]

Interesting solution - thanks!

I haven't seen nested IF statements like that either. All good.

[tcroberts]

Yea, there are more concise ways to write nested IF statements if you need, like ELSEIF, or if there are many conditions, a switch statement.

I just find it easier to use the more verbose method when writing solutions. as it is easier for me personally to follow the logic, provide explanations, or identify any issues with it.

In general, the ELSEIF or SWITCH() syntax is probably better to use for production though.

Glad I could help (: