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

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

Reshape.yxmd