Transpose every n rows (multiple columns)

[thrnma]

Hello,

I go into a directory and pull all the excel files which contain a specific sheet and it puts the data in the following format

Completion	Value
Yes	£100
Yes	£50
No	£0
Completion	Value
Yes	£150
Yes	£25
No	£0
Completion	Value
Yes	£300
Yes	£5
No	£0
Completion	Value
Yes	£125
Yes	£125
No	£0

I would like to be able to split the data every time it gets to the title column (Completion and Value) and then copy it over to a new column, so it looks like this:

Completion	Value	Completion	Value	Completion	Value	Completion	Value
Yes	£100	Yes	£150	Yes	£300	Yes	£125
Yes	£50	Yes	£25	Yes	£5	Yes	£125
No	£0	No	£0	No	£0	No	£0

I need to do it an undefined number of times (as some months there will be 4 data sets, the next it could be 40), so ideally the workflow should be scalable.

Please can someone help me to do this?

Thank you

[jdunkerley79 · Accepted solution]

I would suggest:

Start with a couple of multi-row formulas to create a Group (blocks of 4) and a Row (1-4 within a Group)

Then Transpose the two fields

Make a new name which consists of Group and Field Name

Cross tab to make the table form

Sample attached

[RolandSchubert]

Hi @thrnma ,

if the number of rows does not change, you could use the Make Column tool:

Let me know if it works for you.

Best,

Roland

[jdunkerley79]

Hadn't seen the MakeColumns tool before - don't spend enough time in the lab!

Good solution

[thrnma]

@jdunkerley79 

Thank you for this. I used it to fit to my bigger data set that I am using and it split the data where I needed it to, but then it did not put the columns next to each other like in your workflow

Instead it put the first 66 rows, then made a new column and put the next 66 rows in row 67-132 and then the next 66 rows in row 133-195.

Do you know why it has done this?

[thrnma]

@RolandSchubert 

Thank you for this. Is there a way that I can get it to automatically change the number of columns. In this example, I need 4 sets of columns, but do I have to manually change the number of columns in the make columns tool if I want to do it for, say, 15 columns or can I set it up to change automatically? Maybe based on how many times a word appears in a column (such as make as many columns as the word Value appears)?

[jdunkerley79 · Accepted solution]

On the second Multi Row formula tool make sure Group is ticked, then I think it will work.

[thrnma]

@jdunkerley79 

Brilliant! That is perfect, thank you!

[vizAlter]

@RolandSchubert — Good use of "Make Columns".  👍