how to re-layout data

[netfan]

hi guys,
here i got a question as below. can anybody kindly help on this? thank
in adv.
Original layout:
ColumnA ColumnB ColumnC
2006-6-28 9:00 24465
2006-6-28 12:00 25609
2006-6-28 15:00 27295
2006-6-29 9:00 27309
2006-6-29 12:00 27312
2006-6-29 15:00 24465
2006-6-30 9:00 27295
2006-6-30 12:00 27302
2006-6-30 15:00 27309
......
I wanna re-layout it listed in this format:
NewColumnA NewColumnB(9:00) NewColumnC(12:00) NewColumnD(15:00)
2006-6-28 24465(ColumnC1) 25609(ColumnC2) 27295(ColumnC3)

2006-6-29 27309(ColumnC4) 27312(ColumnC5) 24465(ColumnC6)
2006-6-30 27295(ColumnC7) 27302(ColumnC8) 27309(ColumnC9)
......

if there is any method or function to do this automatically

 

[Guest]

If this is a huge file, or if you need to "relayout" this data on an ongoing
basis, then you could set up a new tab with a bunch of Vlookup functions to
pull in the new data. But assuming you just need to change this once, and
the file is not too big, here's a manual approach that would work:

Fill the new Column A with all the dates. The auto fill works for this (you
establish the first two dates, then highlight those two cells, then grab the
little block on the bottom right corner of the highlighted rectangle, and
drag down) or you could use the Advanced Filter on your existing data to copy
(unique records only) into the new Column A.

Once that is done, go to your existing Column C and copy the first three
cells of data. Now go to your new Column B and click into the cell for that
date, then go to Edit . . . Paste Special. Click the checkbox called
"Transpose" which will switch your "vertical" cells to "horizontal", thus
filling new Columns B, C, and D.

Repeat for all the dates in your old spreadsheet.

Hope that helps, but, again, if this is a huge data set or you have to do
this conversion regularly, then write back for a more permanent solution.

 

[netfan]

Dear andy,
thanks for ur reply. as you imagined, it's a huge file. so i have to
find some way to do with it. any more suggestion pls?

 

[Guest]

Do you ever get an answer to this? You can create a second sheet (sheet2)
which looks up all your data from the first sheet (sheet1), as follows:

First create a second sheet with the following headers in columns A-D:

Date 9:00 12:00 15:00

In column A you'll need to fill in all the dates from your original sheet
(each date needs to appear only once). Should be easy if they are
continuous, or you can use the Advanced Filter to get a list of all unique
values, then paste that list in under the Date header.

Now in cell B2 paste the following formula:

=SUMPRODUCT((Sheet1!$A$1:$A$1000=$A2)*(Sheet1!$B$1:$B$1000=B$1)*(Sheet1!$C$1:$C$1000))

You'll need to change the sheet references if yours are renamed, and if your
sheet1 has more than 1000 rows then bump up those limits in the formula. But
don't alter the $ signs, they are placed to freeze some of the ranges and
allow others to flex when copied. After customizing your formula in B2, you
can copy it across to C2 and D2, verify that it is working, then copy that
set of three formulas down to all your date rows.

Hope that helps (albeit late).