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 1Dimension 2Category 1Amount 1Category 2Amount 2Category 3Amount 3RandomWordExport30HR50Cash500RandomTestImport40Finance60 SomethingElseTax50 To This Dimension 1Dimension 2CategoryAmountRandomWordExport30RandomWordHR50RandomWordCash500RandomTestImport40RandomTestFinance60SomethingElseTax50 How can I do this?

Tranposing multiple columns with String and Numeric Values

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?

Transformation

Preparation

Accepted answers

tcroberts

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.