Freezing first line columns to rows

[Lauritsjust]

Hi all

I am trying to "freeze" the first column and move other columns to a new row with the frozen column as an account identifier in the first line. 

My input looks something like this:

and I would like my output to look like this:

However, when I do it, it keeps putting the new rows in different columns instead of column 2-4.

Any suggestions? 

[messi007]

@Lauritsjust,

Please see below :

Attached the workflow,

Hope this helps!

Regards

[Ben_H · Accepted solution]

Hi @Lauritsjust,

This is the way I would approach it -

I transposed the data, and then removed the numbers from the field names (ISIN1,2,3), tidied up the data a little and then cross tabbed back to the format you want.

Sorry for the brief explaination, I'm short of time - It should be pretty easy to follow if you download the attachment.

Regards,

Ben

[atcodedog05 · Accepted solution]

Hi @Lauritsjust 

Here is a dynamic solution that would accommodate n columns.

Workflow:

Hope this helps : )

[Lauritsjust]

Thanks for the reply. Even though it is a good solution, I might need to rephrase the question.

I forgot to mention that I have more than 500 columns so the solution provided would be very time consuming.

Do you have an idea for a solution to this problem?

[atcodedog05]

Hi @Lauritsjust 

Solutions provided by me and @Ben_H are the ideal ways of solving this. There is no other dynamic method.

Mine would be slightly faster than I am using multi-row formula instead of the tiles tool.

@Lauritsjust updated it with screenshot

Hope this helps : )

[Lauritsjust]

This is it, but when applying the solution to the data I realized that i made a mistake when creating the test input:

This is more fitting

Is there a way to name column 2+n*3 to ISINn and column 3+n*3 to AMOUNTn and so fourth? This would make the solution usable.

[OllieClarke]

Hey @Lauritsjust 

here's a solution which should scale if you get more columns added:

[Lauritsjust]

This is it, but when applying the solution to the data I realized that i made a mistake when creating the test input:

This is more fitting

Is there a way to name column 2+n*3 to ISINn and column 3+n*3 to AMOUNTn and so fourth? This would make the solution usable.

[OllieClarke]

Hey @Lauritsjust 

This version will take the headers dynamically from their order (assuming 1 is always account).