Match Data to dispense from vertical to horizontal

[Catalytic_Ocelot]

Hello,

So this is kind of hard to explain, but I'm mostly certain that there must be a way to do it in excel. It might not be possible using a single cell to pull the data, but let's see.

I've got three columns with the third being the dependent data. The first column represents the day (dd/mm/yyyy), and it repeats itself 4,320 times until the next day (so it won't ever repeat itself again after it switches in the y-direction). This is because the next column is recording data signals in 20 second intervals (hh:mm:ss) which totals to 4,320 data points per day. This set of 4,320 data points will obviously start over and repeat itself every time the former column moves on to the next day. The third column will vary with each application, and only matters because I would like to pull its value into horizontally categorized format.

I want the days (dd/mm/yyyy) to be on the x-axis with the time interval data points on the y-axis (starting at 00:00:00 and ending 23:59:40). I've already written the excel formulas to represent the individual days on the x-axis, and the y-axis was simple since it's always the same bracket.

Sounds simple right? My problem is that the data points are always starting at different time intervals so it's not as simple as copying the first 4,320 points into the first column, and the next 4,320 points into the following, etc.

Let's say my current data sample has begun at 11:59:00, that means I want the first 2,157 (((11*60)+59)*3) data points to be blank or equal zero. I want to be able to paste my data into the same column format as mentioned, beginning at varying time (column B) intervals, and have it push the data into horizontal format.

Here's what it looks like (on sheet 1): Let's pretend that the day ends at 00:02:00 for the sake of me not having to type out 4,320 lines.

07/28/2015 00:00:40 0
07/28/2015 00:01:00 0
07/28/2015 00:01:20 1
07/28/2015 00:01:40 4
07/28/2015 00:02:00 0
07/29/2015 00:00:00 1
07/29/2015 00:00:20 6
07/29/2015 00:00:40 7
07/29/2015 00:01:00 25
07/29/2015 00:01:20 125
07/29/2015 00:01:40 36
07/29/2015 00:02:00 14
07/30/2015 00:00:00 73
07/30/2015 00:00:20 89
07/30/2015 00:00:40 24
07/30/2015 00:01:00 13
07/30/2015 00:01:20 1
07/30/2015 00:01:40 0
07/30/2015 00:02:00 0

I want it to pull onto the second sheet like:

07/28/2015 07/29/2015 07/30/2015
00:00:00 0 1 73
00:00:20 0 6 89
00:00:40 0 7 24
00:01:00 0 25 13
00:01:20 1 125 1
00:01:40 4 36 0
00:02:00 0 14 0

The number of day columns will vary, but will most usually at maximum be 15.

Hoping someone can help. Thanks in advance for your time.

[gak67]

You could try a pivot table.