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.

1/1/2015	0:00	12
1/1/2015	6:00	55
1/1/2015	12:00	32
1/1/2015	18:00	47
2/1/2015	0:00	56
2/1/2015	6:00	67
2/1/2015	12:00	55
2/1/2015	18:00	49
3/1/2015	0:00	42
3/1/2015	6:00	39
3/1/2015	12:00	35
3/1/2015	18:00	31
4/1/2015	0:00	44

<tbody>
</tbody>

Now the data comes in this form

	0:00	6:00	12:00	18:00
1/1/2015	12	55	32	47
2/1/2015	56	67	55	49
3/1/2015	42	39	35	31
4/1/2015	44	etc	etc	etc
5/1/2015	etc	etc	etc	etc
6/1/2015	etc	etc	etc	etc

<tbody>
</tbody>

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