Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data in one column into two columns
I have some dates and times in one column that I would like to line up the
dates with the times in the adjacent column. Please see example below: Present display Would like displayed as this 5/31/07 5/31/07 11:30 11:30 5/31/07 13:20 5/31/07 6/1/07 10:30 13:20 6/1/07 15:30 6/1/07 10:30 6/1/07 15:30 I apreciate the assistance. -- Constance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data in one column into two columns
one method
if 5/31/07 is in A2 in b2 enter =A3 copy B2 and B3 and paste form B4 to the end of your data Use autofilter on column B and select non-blanks. "Constance" wrote: I have some dates and times in one column that I would like to line up the dates with the times in the adjacent column. Please see example below: Present display Would like displayed as this 5/31/07 5/31/07 11:30 11:30 5/31/07 13:20 5/31/07 6/1/07 10:30 13:20 6/1/07 15:30 6/1/07 10:30 6/1/07 15:30 I apreciate the assistance. -- Constance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data in one column into two columns
This formula worked for me:
=OFFSET($A$1,(ROW()*2)-2,0,1,1)+OFFSET($A$1,(ROW()*2)-1,0,1,1) For this formula to work, it must start on the same row that your "present data" range starts. Where the formula says "$A$1", enter the address of the cell that starts the present data range. Also, the formula assumes recognizes the entries as dates and times (not text, for instance); format the formula cells as date/time. Dave O |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data in one column into two columns
That works! Thanks much.
-- Constance "bj" wrote: one method if 5/31/07 is in A2 in b2 enter =A3 copy B2 and B3 and paste form B4 to the end of your data Use autofilter on column B and select non-blanks. "Constance" wrote: I have some dates and times in one column that I would like to line up the dates with the times in the adjacent column. Please see example below: Present display Would like displayed as this 5/31/07 5/31/07 11:30 11:30 5/31/07 13:20 5/31/07 6/1/07 10:30 13:20 6/1/07 15:30 6/1/07 10:30 6/1/07 15:30 I apreciate the assistance. -- Constance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data in one column into two columns
Say your original list is in Column A.
Enter this formula in Say C1: =INDEX($A:$A,2*ROWS($1:1)+COLUMNS($A:A)-2) THEN, copy across to D1. NOW, format C1 to Dates, and D1 to Time. Select *both* C1 and D1, and drag down the 2 cell selection to copy as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Constance" wrote in message ... I have some dates and times in one column that I would like to line up the dates with the times in the adjacent column. Please see example below: Present display Would like displayed as this 5/31/07 5/31/07 11:30 11:30 5/31/07 13:20 5/31/07 6/1/07 10:30 13:20 6/1/07 15:30 6/1/07 10:30 6/1/07 15:30 I apreciate the assistance. -- Constance |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data in one column into two columns
ARGH! My apologies- hope I catch you before you go off on a wild goose
chase. Use this formula instead: =OFFSET($A$1,ROW()-1,0,1,1)+OFFSET($A$1,ROW(),0,1,1) For this formula to work, it must start on the same row that your "present data" range starts. Where the formula says "$A$1", change the $A to the column that holds your present data range (include the $ anchor). Also, the formula assumes recognizes the entries as dates and times (not text, for instance); format the formula cells as date/ time. Dave O |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rearranging data in one column into two columns
Quick way would be to Select the column starting with the first time,
and copy and then paste in the second column next to the first date. Then select both columns, Data Sort, and then delete the rows that start with "time". Constance wrote: I have some dates and times in one column that I would like to line up the dates with the times in the adjacent column. Please see example below: Present display Would like displayed as this 5/31/07 5/31/07 11:30 11:30 5/31/07 13:20 5/31/07 6/1/07 10:30 13:20 6/1/07 15:30 6/1/07 10:30 6/1/07 15:30 I apreciate the assistance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rearranging and Indexing a column array | Excel Discussion (Misc queries) | |||
rearranging data | Excel Worksheet Functions | |||
Rearranging cells into certain columns | Excel Worksheet Functions | |||
Rearranging columns/rows | Excel Discussion (Misc queries) | |||
Rearranging Data Help... | Excel Discussion (Misc queries) |