Using Transpose or macro not sure what one will work

I have a very long list (column) of data 48735 lines long, the data is currently sorted by time so in my A column my data is sensor 1 min, sensor 2 ave, sensor 1 max, sensor 2 min, sensor 2 ave, sensor 2 max, sensor 3 min, sensor 3 ave, sensor 3 max one reading for each min then repeating for the total length of record time. So I want to take the D column of data and transpose it into 9 separate columns, one for each of the different readings. I have not found a may to do it using the basic transpose function, so maybe I should use a macro, program but I have never written one before so don't know how to start. attached is a picture of the data so you can see what I am working with.

I think I can help you out. Is there any way I could get you to upload your file? I could work with it a lot easier.

Let's see what we can do with out the spreadsheet.

First, insert a column before column E. We're going to use this column to make a list of unique time stamps that are in your list. Put this in E2:

=IFERROR(INDEX($C$2:$C$48736,MATCH(0,INDEX(COUNTIF($E$1:E1,$C$2:$C$48736),0,0),0)),"")

Click the bottom right corner of the cell and copy this down as far as it will go until it runs out of values. Next, we're going to create a formula that matches that timestamp to the header in row 1. Put this in F2:

=INDEX($A$2:$D$48736,MATCH(1,(RIGHT($A$2:$A$48736,8)=F$1)*($C$2:$C$48736=$E2),0),4)

This is an array formula, so confirm it using CTRL+SHIFT+ENTER. Now, again, grab the bottom right corner of the cell, and copy it across to column N. Now select F1:N1, grab the bottom right corner again, and drag it down to the last line that has a value in column E. This should match the values and give you a new list in the format you need. Let me know if you have any questions!

npatridge I tried using the formulas you suggested and I am not getting the results that I want. Maybe I wasn't clear enough in my first post. I would like to condense my data and time column (C) so that each time has one row. the amp reading are separated out into separate columns.

________________C_____________D___________ E__________F______________G
Row 1______date/time______---------------sensor readings in amps---------------
Row 2___3/19/2015 14:27____sensor 1 min___sensor 1 ave___sensor 1 max___sensor 2 min...
row 3____3/19/2015 14:28___sensor 1 min___sensor 1 ave___sensor 1 max___sensor 2 min...

Hm, I thought that was what I had accomplished. Here is the result of mine with the formulas I posted:

snip.png

Is that right?

There are a few answers here. I imagine that Excel has converted your date/time into its date serial format in the background, and that you can change the number format of the column to how you like. However, if it is truly text, you can use the Text to Columns function under Data>Data Tools; set your delimiter to "space." If for some reason neither of those options work, and again, it is definitely text, you can use the following formulas to get everything to the left of the space and the right of the space:

=LEFT(C2,FIND(" ",C2)-1)
=RIGHT(C2,LEN(C2)-FIND(" ",C2))

Have a great evening!