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" wrote:
hi guys,
here i got a question as below. can anybody kindly help on this? thanks
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?
--
netfan
------------------------------------------------------------------------
netfan's Profile: http://www.excelforum.com/member.php...o&userid=31349
View this thread: http://www.excelforum.com/showthread...hreadid=556167