I posted a question and got a reply that did not work. I marked the answer
not helpful to me, and replied to the person who answered. Since I have not
received an answer, I am asking again. Here is the gist of the prior post:

My Question:

I have an Excel file with one column that has entries of both a date and
telephone number for each row of data. The date is in m/d/yyyy format and the
phone is in ###-###-#### format. The problem I have is that when the file was
created the date and all other relevant column information is in one row and
the corresponding telephone is in a second row with no other relevant column
information. So each record consists of two rows. I want to make a column
that will have the telephone number and get rid of the second row that has
only the telephone. Here's an example:

Date and Phone ... B...C...D...F... columns
6/2/2008 x y z m...
555-1212
6/2/2008 f g h j...
555-3333

I'd like to get

Date... Phone ... B...C...D...F... columns
6/2/2008 555-1212 x y z m...
6/2/2008 555-3333 f g h j...

Any ideas?

Joel responded:

The easiest way is to use text to columns. I believe you have a return in
the cell to put the phone number on a 2nd line in the cell. If not, then the
width of the cell was adjusted t force the phone number on a 2nd line. Then
ingore the comments below about the square character.

1) Insert an empty column B.
2) Highlight Column A
3) Use menu Data - Text to Columns
a) Select fixed Width
b) Next
c) Use mouse to add a column by clicking to the left of the square
character between the date and phone number. a Verticle line will appear
which will sperate the data into two columns. The square character is the
return which put the phone number on a second line.

If there is no square character then simply click between the phone number
and date.
d) Press Finish
4) Now remove the return which is an invisiable character in front of the
phone number and any unecessary spaces. You can use Edit - Replace to remove
spaces in the cells. You can also remove the square character using replace.

My reply to Joel:

OK, that was a nice try, but there is no return, and adjusting the width does
not do what you say in your second option. There are simply two rows, no
square character, and you cannot do anything about the separate rows with any
delimiter. They are just two separate rows. If you press Finish, it stays the
same, nothing changes.

I'm still in need of advice. My file is quite large and I'd hate to have to
go through cutting and pasting phone numbers into a new column and deleting
rows throughout the entire file.

I am probably missing sth. but

presume
A1=6/2/2008 x y z m...
A2=555-1212

to get dates insert in D1:
=LEFT(OFFSET($K$1,ROW()*2-2,-10),FIND(" ",OFFSET($K$1,ROW()*2-2,-10),
1)-1)

to get phone numbs insert in E1:
=OFFSET($K$1,ROW()*2-1,-10)

then copy down

HIH

Hi

This macro should do what you need.

Hit ALT + F11 to open the macro editor. Goto "Insert" Module Copy the
code below to the codesheet, and run the macro.


Sub MovePhoneNumbers()
Dim StartCell As Range
Dim SortRange As Range
Set StartCell = Range("A2")

Columns("B").Insert
off = 1
Do Until StartCell.Offset(off, 0).Value = ""
StartCell.Offset(off, 0).Cut StartCell.Offset(off - 1, 1)
off = off + 2
Loop
EndRow = Cells((Rows.Count), "A").End(xlUp).Row
Set SortRange = Range("A1", Cells((EndRow),
StartCell.End(xlToRight).Column))
SortRange.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

Regards,
Per

"Ray S." skrev i meddelelsen
...
I posted a question and got a reply that did not work. I marked the answer
not helpful to me, and replied to the person who answered. Since I have
not
received an answer, I am asking again. Here is the gist of the prior post:

My Question:

I have an Excel file with one column that has entries of both a date and
telephone number for each row of data. The date is in m/d/yyyy format and
the
phone is in ###-###-#### format. The problem I have is that when the file
was
created the date and all other relevant column information is in one row
and
the corresponding telephone is in a second row with no other relevant
column
information. So each record consists of two rows. I want to make a column
that will have the telephone number and get rid of the second row that has
only the telephone. Here's an example:

Date and Phone ... B...C...D...F... columns
6/2/2008 x y z m...
555-1212
6/2/2008 f g h j...
555-3333

I'd like to get

Date... Phone ... B...C...D...F... columns
6/2/2008 555-1212 x y z m...
6/2/2008 555-3333 f g h j...

Any ideas?

Joel responded:

The easiest way is to use text to columns. I believe you have a return in
the cell to put the phone number on a 2nd line in the cell. If not, then
the
width of the cell was adjusted t force the phone number on a 2nd line.
Then
ingore the comments below about the square character.

1) Insert an empty column B.
2) Highlight Column A
3) Use menu Data - Text to Columns
a) Select fixed Width
b) Next
c) Use mouse to add a column by clicking to the left of the square
character between the date and phone number. a Verticle line will appear
which will sperate the data into two columns. The square character is the
return which put the phone number on a second line.

If there is no square character then simply click between the phone number
and date.
d) Press Finish
4) Now remove the return which is an invisiable character in front of the
phone number and any unecessary spaces. You can use Edit - Replace to
remove
spaces in the cells. You can also remove the square character using
replace.

My reply to Joel:

OK, that was a nice try, but there is no return, and adjusting the width
does
not do what you say in your second option. There are simply two rows, no
square character, and you cannot do anything about the separate rows with
any
delimiter. They are just two separate rows. If you press Finish, it stays
the
same, nothing changes.

I'm still in need of advice. My file is quite large and I'd hate to have
to
go through cutting and pasting phone numbers into a new column and
deleting
rows throughout the entire file.

Yes, you are missing it.
A1 contains 6/2/2008
A2 contains 555-1212

all the other columns in row 1 are relevant, but
none of those are reproduced in row2 - except for the phone
number, all other cells in row 2 are blank...and so, every other row.

"Jarek Kujawa" wrote:

I am probably missing sth. but

presume
A1=6/2/2008 x y z m...
A2=555-1212

to get dates insert in D1:
=LEFT(OFFSET($K$1,ROW()*2-2,-10),FIND(" ",OFFSET($K$1,ROW()*2-2,-10),
1)-1)

to get phone numbs insert in E1:
=OFFSET($K$1,ROW()*2-1,-10)

then copy down

HIH

This part of the macro does not compile and returns a syntax error:

Set SortRange = Range("A1", Cells((EndRow),
StartCell.End(xlToRight).Column))

"Per Jessen" wrote:

Hi

This macro should do what you need.

Hit ALT + F11 to open the macro editor. Goto "Insert" Module Copy the
code below to the codesheet, and run the macro.


Sub MovePhoneNumbers()
Dim StartCell As Range
Dim SortRange As Range
Set StartCell = Range("A2")

Columns("B").Insert
off = 1
Do Until StartCell.Offset(off, 0).Value = ""
StartCell.Offset(off, 0).Cut StartCell.Offset(off - 1, 1)
off = off + 2
Loop
EndRow = Cells((Rows.Count), "A").End(xlUp).Row
Set SortRange = Range("A1", Cells((EndRow),
StartCell.End(xlToRight).Column))
SortRange.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

Regards,
Per

"Ray S." skrev i meddelelsen
...
I posted a question and got a reply that did not work. I marked the answer
not helpful to me, and replied to the person who answered. Since I have
not
received an answer, I am asking again. Here is the gist of the prior post:

My Question:

I have an Excel file with one column that has entries of both a date and
telephone number for each row of data. The date is in m/d/yyyy format and
the
phone is in ###-###-#### format. The problem I have is that when the file
was
created the date and all other relevant column information is in one row
and
the corresponding telephone is in a second row with no other relevant
column
information. So each record consists of two rows. I want to make a column
that will have the telephone number and get rid of the second row that has
only the telephone. Here's an example:

Date and Phone ... B...C...D...F... columns
6/2/2008 x y z m...
555-1212
6/2/2008 f g h j...
555-3333

I'd like to get

Date... Phone ... B...C...D...F... columns
6/2/2008 555-1212 x y z m...
6/2/2008 555-3333 f g h j...

Any ideas?

Joel responded:

The easiest way is to use text to columns. I believe you have a return in
the cell to put the phone number on a 2nd line in the cell. If not, then
the
width of the cell was adjusted t force the phone number on a 2nd line.
Then
ingore the comments below about the square character.

1) Insert an empty column B.
2) Highlight Column A
3) Use menu Data - Text to Columns
a) Select fixed Width
b) Next
c) Use mouse to add a column by clicking to the left of the square
character between the date and phone number. a Verticle line will appear
which will sperate the data into two columns. The square character is the
return which put the phone number on a second line.

If there is no square character then simply click between the phone number
and date.
d) Press Finish
4) Now remove the return which is an invisiable character in front of the
phone number and any unecessary spaces. You can use Edit - Replace to
remove
spaces in the cells. You can also remove the square character using
replace.

My reply to Joel:

OK, that was a nice try, but there is no return, and adjusting the width
does
not do what you say in your second option. There are simply two rows, no
square character, and you cannot do anything about the separate rows with
any
delimiter. They are just two separate rows. If you press Finish, it stays
the
same, nothing changes.

I'm still in need of advice. My file is quite large and I'd hate to have
to
go through cutting and pasting phone numbers into a new column and
deleting
rows throughout the entire file.