Re-arranging data sets

[Simmo1313]

Hi All,

I am working with Excel 2016

I have an issue with a data set that I receive regularly which now comes in a different format to what I have set up all my systems for so I am hoping to find a simple fill down formula that will translate it to what I am used to.

Original Data set looked like this. It was actually in half hour increments and was a years worth of data so 17520 rows long.

[TABLE="width: 500"]
<tbody>[TR]
[TD]1/1/2015[/TD]
[TD]0:00[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]1/1/2015[/TD]
[TD]6:00[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]1/1/2015[/TD]
[TD]12:00[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]1/1/2015[/TD]
[TD]18:00[/TD]
[TD]47[/TD]
[/TR]
[TR]
[TD]2/1/2015[/TD]
[TD]0:00[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]2/1/2015[/TD]
[TD]6:00[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]2/1/2015[/TD]
[TD]12:00[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]2/1/2015[/TD]
[TD]18:00[/TD]
[TD]49[/TD]
[/TR]
[TR]
[TD]3/1/2015[/TD]
[TD]0:00[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]3/1/2015[/TD]
[TD]6:00[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD]3/1/2015[/TD]
[TD]12:00[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]3/1/2015[/TD]
[TD]18:00[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]4/1/2015[/TD]
[TD]0:00[/TD]
[TD]44[/TD]
[/TR]
</tbody>[/TABLE]

Now the data comes in this form

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]0:00[/TD]
[TD]6:00[/TD]
[TD]12:00[/TD]
[TD]18:00[/TD]
[/TR]
[TR]
[TD]1/1/2015[/TD]
[TD]12[/TD]
[TD]55[/TD]
[TD]32[/TD]
[TD]47[/TD]
[/TR]
[TR]
[TD]2/1/2015[/TD]
[TD]56[/TD]
[TD]67[/TD]
[TD]55[/TD]
[TD]49[/TD]
[/TR]
[TR]
[TD]3/1/2015[/TD]
[TD]42[/TD]
[TD]39[/TD]
[TD]35[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]4/1/2015[/TD]
[TD]44[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]5/1/2015[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD]6/1/2015[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[/TR]
</tbody>[/TABLE]

This new format is much harder to work with pivot tables which was how we analysed the data previously.

So I am hoping someone might be able to help me out with a fill down formula that picks the start cell (top left), then the one to the right and so on until it gets to the end of the data sets row, then start on the next line down back at the far left and repeat until all data is in the original format we find easier to work with.

Any help or guidance would be appreciated.

Cheers

 

[sheetspread]

Use the reverse pivot table

https://www.youtube.com/watch?v=xmqTN0X-AgY

 

[Simmo1313]

Legend!

That's even better

Thanks very much