Rearranging data in one column into two columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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
 
That works! Thanks much.
--
Constance


bj said:
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.
Click to expand...
 
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!
=====================================================

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.
 
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
 
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".
 
Back
Top