Merging Data in Different Rows

[Guest]

I have a spreadsheet with records of persons with their address and other
information and records of the same persons with their phone numbers. The
rows are sorted by name so that the row with address and the row with phone
number are adjacent. What I need to do is merge the phone number onto the
row with the address and other data and then delete the row with the name and
phone number. Is there a way to do that in Excel? -- Chris

 

[Guest]

There are a couple of ways to do this, I think the easiest is to use a macro
to get the job done. The code below will do it once you make the changes to
the Const values set up at the beginning to go along with the way your
worksheet is set up now. As always, test on a copy of the sheet to begin
with so you don't lose any original information. This is a destructive
process (the .Delete portion), so if one of us doesn't get it right, data
loss is very possible. Just make a copy of your .xls file to try it out in,
that way the original stays in one piece.

To put the code in a module so you can get to it with Tools | Macro |
Macros, open the (copy) workbook and press [Alt]+[F11] to get into the VB
Editor, choose Insert | Module from the VB Editor menu and cut and paste the
code below into that and then close the VB Editor. Choose the sheet with the
information on it and run the macro.

Sub MovePhoneNumbers()
'change these constants to match with
'layout of your sheet
Const ColumnWithNames = "A"
Const oldPhoneColumn = "B" ' move from
Const newPhoneColumn = "C" ' move to
Const firstRowWithAName = 1 ' 2 if you have title/header in row 1

Dim LastRowWithAName As Long
Dim rowNumber As Long

If Val(Left(Application.Version, 2)) < 12 Then
'in pre-2007 Excel
LastRowWithAName = _
Range(ColumnWithNames & Rows.Count).End(xlUp).Row
Else
'in Excel 2007 (or later)
LastRowWithAName = _
Range(ColumnWithNames & Rows.CountLarge).End(xlUp).Row
End If

'this presumes things are as you described:
'two rows per person, 1st row has address info
'2nd row has phone # that needs to be moved and then deleted
For rowNumber = firstRowWithAName To LastRowWithAName Step 2
'do the copy
Range(newPhoneColumn & rowNumber) = _
Range(oldPhoneColumn & rowNumber + 1)
Range(oldPhoneColumn & rowNumber + 1).EntireRow.Delete
rowNumber = rowNumber - 1 ' adjust for deleted row!
Next
End Sub

 

[Guest]

Oops, my first attempt at a reply didn't go through, it seems. Apologize if
it did and this is redundant.

Thanks for this very impressive program. Unfortunately, it didn't work for
me -- partly due, no doubt, to my complete ignorance of Visual Basic (I
couldn't figure out how to deal with the Constant Value correlations -- I
changed the name column title to ColumnWithNames and phone column to
oldPhoneColumn but had no idea what to do with newPhoneColumn or
firstRowWithName) and the fact that my spreadsheet is set up differently than
you assumed based on my first post.

The spreadsheet actually isn't neatly set up with alternating rows, each
pair containing one record without phone and one with. There are many
records w/o phone number that don't have a corresponding record with phone
number, and some records with the same name but no phone number in either.
The spreadsheet is too large (>10,000 rows) to clean it up first.

Any hope for me?

THanks again for your help --

Chris

There are a couple of ways to do this, I think the easiest is to use a macro
to get the job done. The code below will do it once you make the changes to
the Const values set up at the beginning to go along with the way your
worksheet is set up now. As always, test on a copy of the sheet to begin
with so you don't lose any original information. This is a destructive
process (the .Delete portion), so if one of us doesn't get it right, data
loss is very possible. Just make a copy of your .xls file to try it out in,
that way the original stays in one piece.

To put the code in a module so you can get to it with Tools | Macro |
Macros, open the (copy) workbook and press [Alt]+[F11] to get into the VB
Editor, choose Insert | Module from the VB Editor menu and cut and paste the
code below into that and then close the VB Editor. Choose the sheet with the
information on it and run the macro.

Sub MovePhoneNumbers()
'change these constants to match with
'layout of your sheet
Const ColumnWithNames = "A"
Const oldPhoneColumn = "B" ' move from
Const newPhoneColumn = "C" ' move to
Const firstRowWithAName = 1 ' 2 if you have title/header in row 1

Dim LastRowWithAName As Long
Dim rowNumber As Long

If Val(Left(Application.Version, 2)) < 12 Then
'in pre-2007 Excel
LastRowWithAName = _
Range(ColumnWithNames & Rows.Count).End(xlUp).Row
Else
'in Excel 2007 (or later)
LastRowWithAName = _
Range(ColumnWithNames & Rows.CountLarge).End(xlUp).Row
End If

'this presumes things are as you described:
'two rows per person, 1st row has address info
'2nd row has phone # that needs to be moved and then deleted
For rowNumber = firstRowWithAName To LastRowWithAName Step 2
'do the copy
Range(newPhoneColumn & rowNumber) = _
Range(oldPhoneColumn & rowNumber + 1)
Range(oldPhoneColumn & rowNumber + 1).EntireRow.Delete
rowNumber = rowNumber - 1 ' adjust for deleted row!
Next
End Sub

I have a spreadsheet with records of persons with their address and other
information and records of the same persons with their phone numbers. The
rows are sorted by name so that the row with address and the row with phone
number are adjacent. What I need to do is merge the phone number onto the
row with the address and other data and then delete the row with the name and
phone number. Is there a way to do that in Excel? -- Chris

 

[Guest]

Of course there is hope - there is always hope for those who are willing to
try!


BEFORE I go on, seeing that you have a huge amount of data, make sure you
try out any code that I give you, or that anyone else gives you, on a copy of
your original workbook. I'd hate to see a small oversight destroy your
database.

The code does not require you to change anything on your worksheets at all!
What it allows you to do is define things the way that your worksheet is set
up. The
Const ColumnWithNames = "A"
was set up to let you tell the program which column on the sheet has the
person's names in it. We will really need that now - before all it was used
for was to determine how far down your worksheet the list went. Now we will
be comparing names on rows to see which ones to move phone numbers for.

oldPhoneColumn is used for you to tell which column the phone number is in
that you want moved into the 'new' record (added to existing information).
And newPhoneColumn is used to tell it which column to move the phone number
to (presumably in the row above where we find oldPhoneColumn number.

Here is a kind of simple graphic example of how it would have worked:
Before:
A B C
1 Jones 1 Main
2 Jones 555-1212
3 Smith 10 Downing
4 Smith 800-0911

A B C
1 Jones 1 Main 555-1212
2 Smith 10 Downing 800-0911

So you see that the phone numbers got moved from B (oldPhoneColumn) up 1 row
and over into C (newPhoneColumn).

To explain a little more about how Const and variables work: a Const is just
a variable that is not allowed to be changed within the program once it is
running. Both constants and variables contain information that can be
refered to by their name, so if I were to tell VB to Print oldPhoneColumn
it would print B. You do not have to change anything on your worksheets at
all - you just have to tell the code where things are!

I will revise the code to look for 2 rows where the names are the same in
adjacent rows (as 1 & 2, 3 & 4, etc) and when it finds two exactly alike,
copy the phone number from the second row up into the first row and then
delete the 2nd row. If it does not find the match, it will just move on down
the sheet looking for more pairs.

Hopefully, the name with the phone number to be moved will always end up on
the 2nd row? And, even more hopefully, the names will have been spelled in
the same manner - I can compare "John Jones" to "john jones" and to "JOHN
JONES" easily enough, but I cannot match "John Jones" up with "John Q.
Jones".

You did say in your initial post "The rows are sorted by name so that the
row with address and the row with phone number are adjacent." I presume that
is still (generally) a true statement? Realizing now that there may be some
entries with only one row of information.

Oops, my first attempt at a reply didn't go through, it seems. Apologize if
it did and this is redundant.

Thanks for this very impressive program. Unfortunately, it didn't work for
me -- partly due, no doubt, to my complete ignorance of Visual Basic (I
couldn't figure out how to deal with the Constant Value correlations -- I
changed the name column title to ColumnWithNames and phone column to
oldPhoneColumn but had no idea what to do with newPhoneColumn or
firstRowWithName) and the fact that my spreadsheet is set up differently than
you assumed based on my first post.

The spreadsheet actually isn't neatly set up with alternating rows, each
pair containing one record without phone and one with. There are many
records w/o phone number that don't have a corresponding record with phone
number, and some records with the same name but no phone number in either.
The spreadsheet is too large (>10,000 rows) to clean it up first.

Any hope for me?

THanks again for your help --

Chris

There are a couple of ways to do this, I think the easiest is to use a macro
to get the job done. The code below will do it once you make the changes to
the Const values set up at the beginning to go along with the way your
worksheet is set up now. As always, test on a copy of the sheet to begin
with so you don't lose any original information. This is a destructive
process (the .Delete portion), so if one of us doesn't get it right, data
loss is very possible. Just make a copy of your .xls file to try it out in,
that way the original stays in one piece.

To put the code in a module so you can get to it with Tools | Macro |
Macros, open the (copy) workbook and press [Alt]+[F11] to get into the VB
Editor, choose Insert | Module from the VB Editor menu and cut and paste the
code below into that and then close the VB Editor. Choose the sheet with the
information on it and run the macro.

Sub MovePhoneNumbers()
'change these constants to match with
'layout of your sheet
Const ColumnWithNames = "A"
Const oldPhoneColumn = "B" ' move from
Const newPhoneColumn = "C" ' move to
Const firstRowWithAName = 1 ' 2 if you have title/header in row 1

Dim LastRowWithAName As Long
Dim rowNumber As Long

If Val(Left(Application.Version, 2)) < 12 Then
'in pre-2007 Excel
LastRowWithAName = _
Range(ColumnWithNames & Rows.Count).End(xlUp).Row
Else
'in Excel 2007 (or later)
LastRowWithAName = _
Range(ColumnWithNames & Rows.CountLarge).End(xlUp).Row
End If

'this presumes things are as you described:
'two rows per person, 1st row has address info
'2nd row has phone # that needs to be moved and then deleted
For rowNumber = firstRowWithAName To LastRowWithAName Step 2
'do the copy
Range(newPhoneColumn & rowNumber) = _
Range(oldPhoneColumn & rowNumber + 1)
Range(oldPhoneColumn & rowNumber + 1).EntireRow.Delete
rowNumber = rowNumber - 1 ' adjust for deleted row!
Next
End Sub

I have a spreadsheet with records of persons with their address and other
information and records of the same persons with their phone numbers. The
rows are sorted by name so that the row with address and the row with phone
number are adjacent. What I need to do is merge the phone number onto the
row with the address and other data and then delete the row with the name and
phone number. Is there a way to do that in Excel? -- Chris

 

[Guest]

Hopefully, the name with the phone number to be moved will always end up on
the 2nd row? And, even more hopefully, the names will have been spelled in
the same manner - I can compare "John Jones" to "john jones" and to "JOHN
JONES" easily enough, but I cannot match "John Jones" up with "John Q.
Jones".

CC: The name with the phone number is almost always on the 2nd row -- there
seem to be occasional examples where it is reversed because the names don't
match up exactly -- for example, one has a middle initial. But in probably
95% of cases they do line up consistently -- and that's good enough for my
purposes.

You did say in your initial post "The rows are sorted by name so that the
row with address and the row with phone number are adjacent." I presume that
is still (generally) a true statement? Realizing now that there may be some
entries with only one row of information.

CC: Yes, they are, as noted above, normally adjacent. However, there are
not only some rows with a phone record without a corresponding address
record, but there are many, many rows with an address record and no
corresponding phone record. To give a sense of it, of the first 50 rows,
only 12 qualify as phone and address records adjacent (6 pairs). What I'd
like to end up with is all the phone numbers either assigned to an address
record, or deleted. Then I can sort the list by phone and delete all the
unpaired rows. Doable?

Chris

Of course there is hope - there is always hope for those who are willing to
try!


BEFORE I go on, seeing that you have a huge amount of data, make sure you
try out any code that I give you, or that anyone else gives you, on a copy of
your original workbook. I'd hate to see a small oversight destroy your
database.

The code does not require you to change anything on your worksheets at all!
What it allows you to do is define things the way that your worksheet is set
up. The
Const ColumnWithNames = "A"
was set up to let you tell the program which column on the sheet has the
person's names in it. We will really need that now - before all it was used
for was to determine how far down your worksheet the list went. Now we will
be comparing names on rows to see which ones to move phone numbers for.

oldPhoneColumn is used for you to tell which column the phone number is in
that you want moved into the 'new' record (added to existing information).
And newPhoneColumn is used to tell it which column to move the phone number
to (presumably in the row above where we find oldPhoneColumn number.

Here is a kind of simple graphic example of how it would have worked:
Before:
A B C
1 Jones 1 Main
2 Jones 555-1212
3 Smith 10 Downing
4 Smith 800-0911

A B C
1 Jones 1 Main 555-1212
2 Smith 10 Downing 800-0911

So you see that the phone numbers got moved from B (oldPhoneColumn) up 1 row
and over into C (newPhoneColumn).

To explain a little more about how Const and variables work: a Const is just
a variable that is not allowed to be changed within the program once it is
running. Both constants and variables contain information that can be
refered to by their name, so if I were to tell VB to Print oldPhoneColumn
it would print B. You do not have to change anything on your worksheets at
all - you just have to tell the code where things are!

I will revise the code to look for 2 rows where the names are the same in
adjacent rows (as 1 & 2, 3 & 4, etc) and when it finds two exactly alike,
copy the phone number from the second row up into the first row and then
delete the 2nd row. If it does not find the match, it will just move on down
the sheet looking for more pairs.

Hopefully, the name with the phone number to be moved will always end up on
the 2nd row? And, even more hopefully, the names will have been spelled in
the same manner - I can compare "John Jones" to "john jones" and to "JOHN
JONES" easily enough, but I cannot match "John Jones" up with "John Q.
Jones".

You did say in your initial post "The rows are sorted by name so that the
row with address and the row with phone number are adjacent." I presume that
is still (generally) a true statement? Realizing now that there may be some
entries with only one row of information.

Oops, my first attempt at a reply didn't go through, it seems. Apologize if
it did and this is redundant.

Thanks for this very impressive program. Unfortunately, it didn't work for
me -- partly due, no doubt, to my complete ignorance of Visual Basic (I
couldn't figure out how to deal with the Constant Value correlations -- I
changed the name column title to ColumnWithNames and phone column to
oldPhoneColumn but had no idea what to do with newPhoneColumn or
firstRowWithName) and the fact that my spreadsheet is set up differently than
you assumed based on my first post.

The spreadsheet actually isn't neatly set up with alternating rows, each
pair containing one record without phone and one with. There are many
records w/o phone number that don't have a corresponding record with phone
number, and some records with the same name but no phone number in either.
The spreadsheet is too large (>10,000 rows) to clean it up first.

Any hope for me?

THanks again for your help --

Chris

There are a couple of ways to do this, I think the easiest is to use a macro
to get the job done. The code below will do it once you make the changes to
the Const values set up at the beginning to go along with the way your
worksheet is set up now. As always, test on a copy of the sheet to begin
with so you don't lose any original information. This is a destructive
process (the .Delete portion), so if one of us doesn't get it right, data
loss is very possible. Just make a copy of your .xls file to try it out in,
that way the original stays in one piece.

To put the code in a module so you can get to it with Tools | Macro |
Macros, open the (copy) workbook and press [Alt]+[F11] to get into the VB
Editor, choose Insert | Module from the VB Editor menu and cut and paste the
code below into that and then close the VB Editor. Choose the sheet with the
information on it and run the macro.

Sub MovePhoneNumbers()
'change these constants to match with
'layout of your sheet
Const ColumnWithNames = "A"
Const oldPhoneColumn = "B" ' move from
Const newPhoneColumn = "C" ' move to
Const firstRowWithAName = 1 ' 2 if you have title/header in row 1

Dim LastRowWithAName As Long
Dim rowNumber As Long

If Val(Left(Application.Version, 2)) < 12 Then
'in pre-2007 Excel
LastRowWithAName = _
Range(ColumnWithNames & Rows.Count).End(xlUp).Row
Else
'in Excel 2007 (or later)
LastRowWithAName = _
Range(ColumnWithNames & Rows.CountLarge).End(xlUp).Row
End If

'this presumes things are as you described:
'two rows per person, 1st row has address info
'2nd row has phone # that needs to be moved and then deleted
For rowNumber = firstRowWithAName To LastRowWithAName Step 2
'do the copy
Range(newPhoneColumn & rowNumber) = _
Range(oldPhoneColumn & rowNumber + 1)
Range(oldPhoneColumn & rowNumber + 1).EntireRow.Delete
rowNumber = rowNumber - 1 ' adjust for deleted row!
Next
End Sub


:

I have a spreadsheet with records of persons with their address and other
information and records of the same persons with their phone numbers. The
rows are sorted by name so that the row with address and the row with phone
number are adjacent. What I need to do is merge the phone number onto the
row with the address and other data and then delete the row with the name and
phone number. Is there a way to do that in Excel? -- Chris

 

[Guest]

Hi, I'm just checking back in. Are you still hoping to help me with revising
the VB Script? I have to finish this project by Saturday evening so wanted
to check back -- sorry for impatience. You've been very helpful and patient
with me! -- Chris

Hopefully, the name with the phone number to be moved will always end up on
the 2nd row? And, even more hopefully, the names will have been spelled in
the same manner - I can compare "John Jones" to "john jones" and to "JOHN
JONES" easily enough, but I cannot match "John Jones" up with "John Q.
Jones".

CC: The name with the phone number is almost always on the 2nd row -- there
seem to be occasional examples where it is reversed because the names don't
match up exactly -- for example, one has a middle initial. But in probably
95% of cases they do line up consistently -- and that's good enough for my
purposes.

You did say in your initial post "The rows are sorted by name so that the
row with address and the row with phone number are adjacent." I presume that
is still (generally) a true statement? Realizing now that there may be some
entries with only one row of information.

CC: Yes, they are, as noted above, normally adjacent. However, there are
not only some rows with a phone record without a corresponding address
record, but there are many, many rows with an address record and no
corresponding phone record. To give a sense of it, of the first 50 rows,
only 12 qualify as phone and address records adjacent (6 pairs). What I'd
like to end up with is all the phone numbers either assigned to an address
record, or deleted. Then I can sort the list by phone and delete all the
unpaired rows. Doable?

Chris

Of course there is hope - there is always hope for those who are willing to
try!


BEFORE I go on, seeing that you have a huge amount of data, make sure you
try out any code that I give you, or that anyone else gives you, on a copy of
your original workbook. I'd hate to see a small oversight destroy your
database.

The code does not require you to change anything on your worksheets at all!
What it allows you to do is define things the way that your worksheet is set
up. The
Const ColumnWithNames = "A"
was set up to let you tell the program which column on the sheet has the
person's names in it. We will really need that now - before all it was used
for was to determine how far down your worksheet the list went. Now we will
be comparing names on rows to see which ones to move phone numbers for.

oldPhoneColumn is used for you to tell which column the phone number is in
that you want moved into the 'new' record (added to existing information).
And newPhoneColumn is used to tell it which column to move the phone number
to (presumably in the row above where we find oldPhoneColumn number.

Here is a kind of simple graphic example of how it would have worked:
Before:
A B C
1 Jones 1 Main
2 Jones 555-1212
3 Smith 10 Downing
4 Smith 800-0911

A B C
1 Jones 1 Main 555-1212
2 Smith 10 Downing 800-0911

So you see that the phone numbers got moved from B (oldPhoneColumn) up 1 row
and over into C (newPhoneColumn).

To explain a little more about how Const and variables work: a Const is just
a variable that is not allowed to be changed within the program once it is
running. Both constants and variables contain information that can be
refered to by their name, so if I were to tell VB to Print oldPhoneColumn
it would print B. You do not have to change anything on your worksheets at
all - you just have to tell the code where things are!

I will revise the code to look for 2 rows where the names are the same in
adjacent rows (as 1 & 2, 3 & 4, etc) and when it finds two exactly alike,
copy the phone number from the second row up into the first row and then
delete the 2nd row. If it does not find the match, it will just move on down
the sheet looking for more pairs.

Hopefully, the name with the phone number to be moved will always end up on
the 2nd row? And, even more hopefully, the names will have been spelled in
the same manner - I can compare "John Jones" to "john jones" and to "JOHN
JONES" easily enough, but I cannot match "John Jones" up with "John Q.
Jones".

You did say in your initial post "The rows are sorted by name so that the
row with address and the row with phone number are adjacent." I presume that
is still (generally) a true statement? Realizing now that there may be some
entries with only one row of information.

Oops, my first attempt at a reply didn't go through, it seems. Apologize if
it did and this is redundant.

Thanks for this very impressive program. Unfortunately, it didn't work for
me -- partly due, no doubt, to my complete ignorance of Visual Basic (I
couldn't figure out how to deal with the Constant Value correlations -- I
changed the name column title to ColumnWithNames and phone column to
oldPhoneColumn but had no idea what to do with newPhoneColumn or
firstRowWithName) and the fact that my spreadsheet is set up differently than
you assumed based on my first post.

The spreadsheet actually isn't neatly set up with alternating rows, each
pair containing one record without phone and one with. There are many
records w/o phone number that don't have a corresponding record with phone
number, and some records with the same name but no phone number in either.
The spreadsheet is too large (>10,000 rows) to clean it up first.

Any hope for me?

THanks again for your help --

Chris


:

There are a couple of ways to do this, I think the easiest is to use a macro
to get the job done. The code below will do it once you make the changes to
the Const values set up at the beginning to go along with the way your
worksheet is set up now. As always, test on a copy of the sheet to begin
with so you don't lose any original information. This is a destructive
process (the .Delete portion), so if one of us doesn't get it right, data
loss is very possible. Just make a copy of your .xls file to try it out in,
that way the original stays in one piece.

To put the code in a module so you can get to it with Tools | Macro |
Macros, open the (copy) workbook and press [Alt]+[F11] to get into the VB
Editor, choose Insert | Module from the VB Editor menu and cut and paste the
code below into that and then close the VB Editor. Choose the sheet with the
information on it and run the macro.

Sub MovePhoneNumbers()
'change these constants to match with
'layout of your sheet
Const ColumnWithNames = "A"
Const oldPhoneColumn = "B" ' move from
Const newPhoneColumn = "C" ' move to
Const firstRowWithAName = 1 ' 2 if you have title/header in row 1

Dim LastRowWithAName As Long
Dim rowNumber As Long

If Val(Left(Application.Version, 2)) < 12 Then
'in pre-2007 Excel
LastRowWithAName = _
Range(ColumnWithNames & Rows.Count).End(xlUp).Row
Else
'in Excel 2007 (or later)
LastRowWithAName = _
Range(ColumnWithNames & Rows.CountLarge).End(xlUp).Row
End If

'this presumes things are as you described:
'two rows per person, 1st row has address info
'2nd row has phone # that needs to be moved and then deleted
For rowNumber = firstRowWithAName To LastRowWithAName Step 2
'do the copy
Range(newPhoneColumn & rowNumber) = _
Range(oldPhoneColumn & rowNumber + 1)
Range(oldPhoneColumn & rowNumber + 1).EntireRow.Delete
rowNumber = rowNumber - 1 ' adjust for deleted row!
Next
End Sub


:

I have a spreadsheet with records of persons with their address and other
information and records of the same persons with their phone numbers. The
rows are sorted by name so that the row with address and the row with phone
number are adjacent. What I need to do is merge the phone number onto the
row with the address and other data and then delete the row with the name and
phone number. Is there a way to do that in Excel? -- Chris

 

[Guest]

Replied to your email - system seems to have forgotten I told it to tell me
about responses. I'll kick it in proper attention-getting location again.

Hi, I'm just checking back in. Are you still hoping to help me with revising
the VB Script? I have to finish this project by Saturday evening so wanted
to check back -- sorry for impatience. You've been very helpful and patient
with me! -- Chris

Hopefully, the name with the phone number to be moved will always end up on
the 2nd row? And, even more hopefully, the names will have been spelled in
the same manner - I can compare "John Jones" to "john jones" and to "JOHN
JONES" easily enough, but I cannot match "John Jones" up with "John Q.
Jones".

CC: The name with the phone number is almost always on the 2nd row -- there
seem to be occasional examples where it is reversed because the names don't
match up exactly -- for example, one has a middle initial. But in probably
95% of cases they do line up consistently -- and that's good enough for my
purposes.

You did say in your initial post "The rows are sorted by name so that the
row with address and the row with phone number are adjacent." I presume that
is still (generally) a true statement? Realizing now that there may be some
entries with only one row of information.

CC: Yes, they are, as noted above, normally adjacent. However, there are
not only some rows with a phone record without a corresponding address
record, but there are many, many rows with an address record and no
corresponding phone record. To give a sense of it, of the first 50 rows,
only 12 qualify as phone and address records adjacent (6 pairs). What I'd
like to end up with is all the phone numbers either assigned to an address
record, or deleted. Then I can sort the list by phone and delete all the
unpaired rows. Doable?

Chris

Of course there is hope - there is always hope for those who are willing to
try!


BEFORE I go on, seeing that you have a huge amount of data, make sure you
try out any code that I give you, or that anyone else gives you, on a copy of
your original workbook. I'd hate to see a small oversight destroy your
database.

The code does not require you to change anything on your worksheets at all!
What it allows you to do is define things the way that your worksheet is set
up. The
Const ColumnWithNames = "A"
was set up to let you tell the program which column on the sheet has the
person's names in it. We will really need that now - before all it was used
for was to determine how far down your worksheet the list went. Now we will
be comparing names on rows to see which ones to move phone numbers for.

oldPhoneColumn is used for you to tell which column the phone number is in
that you want moved into the 'new' record (added to existing information).
And newPhoneColumn is used to tell it which column to move the phone number
to (presumably in the row above where we find oldPhoneColumn number.

Here is a kind of simple graphic example of how it would have worked:
Before:
A B C
1 Jones 1 Main
2 Jones 555-1212
3 Smith 10 Downing
4 Smith 800-0911

A B C
1 Jones 1 Main 555-1212
2 Smith 10 Downing 800-0911

So you see that the phone numbers got moved from B (oldPhoneColumn) up 1 row
and over into C (newPhoneColumn).

To explain a little more about how Const and variables work: a Const is just
a variable that is not allowed to be changed within the program once it is
running. Both constants and variables contain information that can be
refered to by their name, so if I were to tell VB to Print oldPhoneColumn
it would print B. You do not have to change anything on your worksheets at
all - you just have to tell the code where things are!

I will revise the code to look for 2 rows where the names are the same in
adjacent rows (as 1 & 2, 3 & 4, etc) and when it finds two exactly alike,
copy the phone number from the second row up into the first row and then
delete the 2nd row. If it does not find the match, it will just move on down
the sheet looking for more pairs.

Hopefully, the name with the phone number to be moved will always end up on
the 2nd row? And, even more hopefully, the names will have been spelled in
the same manner - I can compare "John Jones" to "john jones" and to "JOHN
JONES" easily enough, but I cannot match "John Jones" up with "John Q.
Jones".

You did say in your initial post "The rows are sorted by name so that the
row with address and the row with phone number are adjacent." I presume that
is still (generally) a true statement? Realizing now that there may be some
entries with only one row of information.

:

Oops, my first attempt at a reply didn't go through, it seems. Apologize if
it did and this is redundant.

Thanks for this very impressive program. Unfortunately, it didn't work for
me -- partly due, no doubt, to my complete ignorance of Visual Basic (I
couldn't figure out how to deal with the Constant Value correlations -- I
changed the name column title to ColumnWithNames and phone column to
oldPhoneColumn but had no idea what to do with newPhoneColumn or
firstRowWithName) and the fact that my spreadsheet is set up differently than
you assumed based on my first post.

The spreadsheet actually isn't neatly set up with alternating rows, each
pair containing one record without phone and one with. There are many
records w/o phone number that don't have a corresponding record with phone
number, and some records with the same name but no phone number in either.
The spreadsheet is too large (>10,000 rows) to clean it up first.

Any hope for me?

THanks again for your help --

Chris


:

There are a couple of ways to do this, I think the easiest is to use a macro
to get the job done. The code below will do it once you make the changes to
the Const values set up at the beginning to go along with the way your
worksheet is set up now. As always, test on a copy of the sheet to begin
with so you don't lose any original information. This is a destructive
process (the .Delete portion), so if one of us doesn't get it right, data
loss is very possible. Just make a copy of your .xls file to try it out in,
that way the original stays in one piece.

To put the code in a module so you can get to it with Tools | Macro |
Macros, open the (copy) workbook and press [Alt]+[F11] to get into the VB
Editor, choose Insert | Module from the VB Editor menu and cut and paste the
code below into that and then close the VB Editor. Choose the sheet with the
information on it and run the macro.

Sub MovePhoneNumbers()
'change these constants to match with
'layout of your sheet
Const ColumnWithNames = "A"
Const oldPhoneColumn = "B" ' move from
Const newPhoneColumn = "C" ' move to
Const firstRowWithAName = 1 ' 2 if you have title/header in row 1

Dim LastRowWithAName As Long
Dim rowNumber As Long

If Val(Left(Application.Version, 2)) < 12 Then
'in pre-2007 Excel
LastRowWithAName = _
Range(ColumnWithNames & Rows.Count).End(xlUp).Row
Else
'in Excel 2007 (or later)
LastRowWithAName = _
Range(ColumnWithNames & Rows.CountLarge).End(xlUp).Row
End If

'this presumes things are as you described:
'two rows per person, 1st row has address info
'2nd row has phone # that needs to be moved and then deleted
For rowNumber = firstRowWithAName To LastRowWithAName Step 2
'do the copy
Range(newPhoneColumn & rowNumber) = _
Range(oldPhoneColumn & rowNumber + 1)
Range(oldPhoneColumn & rowNumber + 1).EntireRow.Delete
rowNumber = rowNumber - 1 ' adjust for deleted row!
Next
End Sub


:

I have a spreadsheet with records of persons with their address and other
information and records of the same persons with their phone numbers. The
rows are sorted by name so that the row with address and the row with phone
number are adjacent. What I need to do is merge the phone number onto the
row with the address and other data and then delete the row with the name and
phone number. Is there a way to do that in Excel? -- Chris

 

[Guest]

I'm sending reply to your email with working test workbook attached and a
..txt file with the code in it so you can modify it as required easily and
then cut and paste into your workbook there.

Here's the code, for all to see. It copies phone number from row below one
being examined and deletes that 2nd row when a match was found. It ignores
all rows immediately following that with the exact same name in them, looking
for a row with a non-matching name and starts the process of match and copy
and delete again from that point.

The key to this is getting the names sorted properly. The name entry with
the address that needs the phone updated in has to be at the top of the group
of same names. So again, I urge you to first test this on a copy of the real
data. I see a good chance to muddy the waters if the name list isn't sorted
'properly' - and of course there's the off chance I've done something wrong
in this even though I have tested it on a short list of 16 names.

Sub MovePhoneNumbers_Rev001()
'change these constants to match with
'layout of your sheet
Const ColumnWithNames = "A"
' column to move from in row below
Const oldPhoneColumn = "B"
' column to move to in row with address
Const newPhoneColumn = "C"
' may be 2 if your list has header/title in row 1
Const firstRowWithAName = 1

Dim LastRowWithAName As Long
Dim addressRow As Long
Dim phoneRow As Long

If Val(Left(Application.Version, 2)) < 12 Then
'in pre-2007 Excel
LastRowWithAName = _
Range(ColumnWithNames & Rows.Count).End(xlUp).Row
Else
'in Excel 2007 (or later)
LastRowWithAName = _
Range(ColumnWithNames & Rows.CountLarge).End(xlUp).Row
End If

addressRow = firstRowWithAName
phoneRow = addressRow + 1

Do Until phoneRow > LastRowWithAName
If UCase(Trim(Range(ColumnWithNames & addressRow))) = _
UCase(Trim(Range(ColumnWithNames & phoneRow))) Then
Range(newPhoneColumn & addressRow) = _
Range(oldPhoneColumn & phoneRow)
Range(oldPhoneColumn & phoneRow).EntireRow.Delete
'find next row with different name
Do Until UCase(Trim(Range(ColumnWithNames & phoneRow))) _
<> UCase(Trim(Range(ColumnWithNames & addressRow)))
phoneRow = phoneRow + 1
If phoneRow > LastRowWithAName Then
Exit Do
End If
Loop
addressRow = phoneRow
phoneRow = addressRow + 1
Else ' no match move down 1 row
addressRow = addressRow + 1
phoneRow = addressRow + 1
End If
Loop
End Sub

Hi, I'm just checking back in. Are you still hoping to help me with revising
the VB Script? I have to finish this project by Saturday evening so wanted
to check back -- sorry for impatience. You've been very helpful and patient
with me! -- Chris

Hopefully, the name with the phone number to be moved will always end up on
the 2nd row? And, even more hopefully, the names will have been spelled in
the same manner - I can compare "John Jones" to "john jones" and to "JOHN
JONES" easily enough, but I cannot match "John Jones" up with "John Q.
Jones".

CC: The name with the phone number is almost always on the 2nd row -- there
seem to be occasional examples where it is reversed because the names don't
match up exactly -- for example, one has a middle initial. But in probably
95% of cases they do line up consistently -- and that's good enough for my
purposes.

You did say in your initial post "The rows are sorted by name so that the
row with address and the row with phone number are adjacent." I presume that
is still (generally) a true statement? Realizing now that there may be some
entries with only one row of information.

CC: Yes, they are, as noted above, normally adjacent. However, there are
not only some rows with a phone record without a corresponding address
record, but there are many, many rows with an address record and no
corresponding phone record. To give a sense of it, of the first 50 rows,
only 12 qualify as phone and address records adjacent (6 pairs). What I'd
like to end up with is all the phone numbers either assigned to an address
record, or deleted. Then I can sort the list by phone and delete all the
unpaired rows. Doable?

Chris

Of course there is hope - there is always hope for those who are willing to
try!


BEFORE I go on, seeing that you have a huge amount of data, make sure you
try out any code that I give you, or that anyone else gives you, on a copy of
your original workbook. I'd hate to see a small oversight destroy your
database.

The code does not require you to change anything on your worksheets at all!
What it allows you to do is define things the way that your worksheet is set
up. The
Const ColumnWithNames = "A"
was set up to let you tell the program which column on the sheet has the
person's names in it. We will really need that now - before all it was used
for was to determine how far down your worksheet the list went. Now we will
be comparing names on rows to see which ones to move phone numbers for.

oldPhoneColumn is used for you to tell which column the phone number is in
that you want moved into the 'new' record (added to existing information).
And newPhoneColumn is used to tell it which column to move the phone number
to (presumably in the row above where we find oldPhoneColumn number.

Here is a kind of simple graphic example of how it would have worked:
Before:
A B C
1 Jones 1 Main
2 Jones 555-1212
3 Smith 10 Downing
4 Smith 800-0911

A B C
1 Jones 1 Main 555-1212
2 Smith 10 Downing 800-0911

So you see that the phone numbers got moved from B (oldPhoneColumn) up 1 row
and over into C (newPhoneColumn).

To explain a little more about how Const and variables work: a Const is just
a variable that is not allowed to be changed within the program once it is
running. Both constants and variables contain information that can be
refered to by their name, so if I were to tell VB to Print oldPhoneColumn
it would print B. You do not have to change anything on your worksheets at
all - you just have to tell the code where things are!

I will revise the code to look for 2 rows where the names are the same in
adjacent rows (as 1 & 2, 3 & 4, etc) and when it finds two exactly alike,
copy the phone number from the second row up into the first row and then
delete the 2nd row. If it does not find the match, it will just move on down
the sheet looking for more pairs.

Hopefully, the name with the phone number to be moved will always end up on
the 2nd row? And, even more hopefully, the names will have been spelled in
the same manner - I can compare "John Jones" to "john jones" and to "JOHN
JONES" easily enough, but I cannot match "John Jones" up with "John Q.
Jones".

You did say in your initial post "The rows are sorted by name so that the
row with address and the row with phone number are adjacent." I presume that
is still (generally) a true statement? Realizing now that there may be some
entries with only one row of information.

:

Oops, my first attempt at a reply didn't go through, it seems. Apologize if
it did and this is redundant.

Thanks for this very impressive program. Unfortunately, it didn't work for
me -- partly due, no doubt, to my complete ignorance of Visual Basic (I
couldn't figure out how to deal with the Constant Value correlations -- I
changed the name column title to ColumnWithNames and phone column to
oldPhoneColumn but had no idea what to do with newPhoneColumn or
firstRowWithName) and the fact that my spreadsheet is set up differently than
you assumed based on my first post.

The spreadsheet actually isn't neatly set up with alternating rows, each
pair containing one record without phone and one with. There are many
records w/o phone number that don't have a corresponding record with phone
number, and some records with the same name but no phone number in either.
The spreadsheet is too large (>10,000 rows) to clean it up first.

Any hope for me?

THanks again for your help --

Chris


:

There are a couple of ways to do this, I think the easiest is to use a macro
to get the job done. The code below will do it once you make the changes to
the Const values set up at the beginning to go along with the way your
worksheet is set up now. As always, test on a copy of the sheet to begin
with so you don't lose any original information. This is a destructive
process (the .Delete portion), so if one of us doesn't get it right, data
loss is very possible. Just make a copy of your .xls file to try it out in,
that way the original stays in one piece.

To put the code in a module so you can get to it with Tools | Macro |
Macros, open the (copy) workbook and press [Alt]+[F11] to get into the VB
Editor, choose Insert | Module from the VB Editor menu and cut and paste the
code below into that and then close the VB Editor. Choose the sheet with the
information on it and run the macro.

Sub MovePhoneNumbers()
'change these constants to match with
'layout of your sheet
Const ColumnWithNames = "A"
Const oldPhoneColumn = "B" ' move from
Const newPhoneColumn = "C" ' move to
Const firstRowWithAName = 1 ' 2 if you have title/header in row 1

Dim LastRowWithAName As Long
Dim rowNumber As Long

If Val(Left(Application.Version, 2)) < 12 Then
'in pre-2007 Excel
LastRowWithAName = _
Range(ColumnWithNames & Rows.Count).End(xlUp).Row
Else
'in Excel 2007 (or later)
LastRowWithAName = _
Range(ColumnWithNames & Rows.CountLarge).End(xlUp).Row
End If

'this presumes things are as you described:
'two rows per person, 1st row has address info
'2nd row has phone # that needs to be moved and then deleted
For rowNumber = firstRowWithAName To LastRowWithAName Step 2
'do the copy
Range(newPhoneColumn & rowNumber) = _
Range(oldPhoneColumn & rowNumber + 1)
Range(oldPhoneColumn & rowNumber + 1).EntireRow.Delete
rowNumber = rowNumber - 1 ' adjust for deleted row!
Next
End Sub


:

I have a spreadsheet with records of persons with their address and other
information and records of the same persons with their phone numbers. The
rows are sorted by name so that the row with address and the row with phone
number are adjacent. What I need to do is merge the phone number onto the
row with the address and other data and then delete the row with the name and
phone number. Is there a way to do that in Excel? -- Chris

 

[Guest]

Thank you so much -- this was EXACTLY what I was looking for!!! -- Chris

I'm sending reply to your email with working test workbook attached and a
.txt file with the code in it so you can modify it as required easily and
then cut and paste into your workbook there.

Here's the code, for all to see. It copies phone number from row below one
being examined and deletes that 2nd row when a match was found. It ignores
all rows immediately following that with the exact same name in them, looking
for a row with a non-matching name and starts the process of match and copy
and delete again from that point.

The key to this is getting the names sorted properly. The name entry with
the address that needs the phone updated in has to be at the top of the group
of same names. So again, I urge you to first test this on a copy of the real
data. I see a good chance to muddy the waters if the name list isn't sorted
'properly' - and of course there's the off chance I've done something wrong
in this even though I have tested it on a short list of 16 names.

Sub MovePhoneNumbers_Rev001()
'change these constants to match with
'layout of your sheet
Const ColumnWithNames = "A"
' column to move from in row below
Const oldPhoneColumn = "B"
' column to move to in row with address
Const newPhoneColumn = "C"
' may be 2 if your list has header/title in row 1
Const firstRowWithAName = 1

Dim LastRowWithAName As Long
Dim addressRow As Long
Dim phoneRow As Long

If Val(Left(Application.Version, 2)) < 12 Then
'in pre-2007 Excel
LastRowWithAName = _
Range(ColumnWithNames & Rows.Count).End(xlUp).Row
Else
'in Excel 2007 (or later)
LastRowWithAName = _
Range(ColumnWithNames & Rows.CountLarge).End(xlUp).Row
End If

addressRow = firstRowWithAName
phoneRow = addressRow + 1

Do Until phoneRow > LastRowWithAName
If UCase(Trim(Range(ColumnWithNames & addressRow))) = _
UCase(Trim(Range(ColumnWithNames & phoneRow))) Then
Range(newPhoneColumn & addressRow) = _
Range(oldPhoneColumn & phoneRow)
Range(oldPhoneColumn & phoneRow).EntireRow.Delete
'find next row with different name
Do Until UCase(Trim(Range(ColumnWithNames & phoneRow))) _
<> UCase(Trim(Range(ColumnWithNames & addressRow)))
phoneRow = phoneRow + 1
If phoneRow > LastRowWithAName Then
Exit Do
End If
Loop
addressRow = phoneRow
phoneRow = addressRow + 1
Else ' no match move down 1 row
addressRow = addressRow + 1
phoneRow = addressRow + 1
End If
Loop
End Sub

Hi, I'm just checking back in. Are you still hoping to help me with revising
the VB Script? I have to finish this project by Saturday evening so wanted
to check back -- sorry for impatience. You've been very helpful and patient
with me! -- Chris

JLatham wrote:
Hopefully, the name with the phone number to be moved will always end up on
the 2nd row? And, even more hopefully, the names will have been spelled in
the same manner - I can compare "John Jones" to "john jones" and to "JOHN
JONES" easily enough, but I cannot match "John Jones" up with "John Q.
Jones".

CC: The name with the phone number is almost always on the 2nd row -- there
seem to be occasional examples where it is reversed because the names don't
match up exactly -- for example, one has a middle initial. But in probably
95% of cases they do line up consistently -- and that's good enough for my
purposes.

You did say in your initial post "The rows are sorted by name so that the
row with address and the row with phone number are adjacent." I presume that
is still (generally) a true statement? Realizing now that there may be some
entries with only one row of information.

CC: Yes, they are, as noted above, normally adjacent. However, there are
not only some rows with a phone record without a corresponding address
record, but there are many, many rows with an address record and no
corresponding phone record. To give a sense of it, of the first 50 rows,
only 12 qualify as phone and address records adjacent (6 pairs). What I'd
like to end up with is all the phone numbers either assigned to an address
record, or deleted. Then I can sort the list by phone and delete all the
unpaired rows. Doable?

Chris

:

Of course there is hope - there is always hope for those who are willing to
try!


BEFORE I go on, seeing that you have a huge amount of data, make sure you
try out any code that I give you, or that anyone else gives you, on a copy of
your original workbook. I'd hate to see a small oversight destroy your
database.

The code does not require you to change anything on your worksheets at all!
What it allows you to do is define things the way that your worksheet is set
up. The
Const ColumnWithNames = "A"
was set up to let you tell the program which column on the sheet has the
person's names in it. We will really need that now - before all it was used
for was to determine how far down your worksheet the list went. Now we will
be comparing names on rows to see which ones to move phone numbers for.

oldPhoneColumn is used for you to tell which column the phone number is in
that you want moved into the 'new' record (added to existing information).
And newPhoneColumn is used to tell it which column to move the phone number
to (presumably in the row above where we find oldPhoneColumn number.

Here is a kind of simple graphic example of how it would have worked:
Before:
A B C
1 Jones 1 Main
2 Jones 555-1212
3 Smith 10 Downing
4 Smith 800-0911

A B C
1 Jones 1 Main 555-1212
2 Smith 10 Downing 800-0911

So you see that the phone numbers got moved from B (oldPhoneColumn) up 1 row
and over into C (newPhoneColumn).

To explain a little more about how Const and variables work: a Const is just
a variable that is not allowed to be changed within the program once it is
running. Both constants and variables contain information that can be
refered to by their name, so if I were to tell VB to Print oldPhoneColumn
it would print B. You do not have to change anything on your worksheets at
all - you just have to tell the code where things are!

I will revise the code to look for 2 rows where the names are the same in
adjacent rows (as 1 & 2, 3 & 4, etc) and when it finds two exactly alike,
copy the phone number from the second row up into the first row and then
delete the 2nd row. If it does not find the match, it will just move on down
the sheet looking for more pairs.

Hopefully, the name with the phone number to be moved will always end up on
the 2nd row? And, even more hopefully, the names will have been spelled in
the same manner - I can compare "John Jones" to "john jones" and to "JOHN
JONES" easily enough, but I cannot match "John Jones" up with "John Q.
Jones".

You did say in your initial post "The rows are sorted by name so that the
row with address and the row with phone number are adjacent." I presume that
is still (generally) a true statement? Realizing now that there may be some
entries with only one row of information.

:

Oops, my first attempt at a reply didn't go through, it seems. Apologize if
it did and this is redundant.

Thanks for this very impressive program. Unfortunately, it didn't work for
me -- partly due, no doubt, to my complete ignorance of Visual Basic (I
couldn't figure out how to deal with the Constant Value correlations -- I
changed the name column title to ColumnWithNames and phone column to
oldPhoneColumn but had no idea what to do with newPhoneColumn or
firstRowWithName) and the fact that my spreadsheet is set up differently than
you assumed based on my first post.

The spreadsheet actually isn't neatly set up with alternating rows, each
pair containing one record without phone and one with. There are many
records w/o phone number that don't have a corresponding record with phone
number, and some records with the same name but no phone number in either.
The spreadsheet is too large (>10,000 rows) to clean it up first.

Any hope for me?

THanks again for your help --

Chris


:

There are a couple of ways to do this, I think the easiest is to use a macro
to get the job done. The code below will do it once you make the changes to
the Const values set up at the beginning to go along with the way your
worksheet is set up now. As always, test on a copy of the sheet to begin
with so you don't lose any original information. This is a destructive
process (the .Delete portion), so if one of us doesn't get it right, data
loss is very possible. Just make a copy of your .xls file to try it out in,
that way the original stays in one piece.

To put the code in a module so you can get to it with Tools | Macro |
Macros, open the (copy) workbook and press [Alt]+[F11] to get into the VB
Editor, choose Insert | Module from the VB Editor menu and cut and paste the
code below into that and then close the VB Editor. Choose the sheet with the
information on it and run the macro.

Sub MovePhoneNumbers()
'change these constants to match with
'layout of your sheet
Const ColumnWithNames = "A"
Const oldPhoneColumn = "B" ' move from
Const newPhoneColumn = "C" ' move to
Const firstRowWithAName = 1 ' 2 if you have title/header in row 1

Dim LastRowWithAName As Long
Dim rowNumber As Long

If Val(Left(Application.Version, 2)) < 12 Then
'in pre-2007 Excel
LastRowWithAName = _
Range(ColumnWithNames & Rows.Count).End(xlUp).Row
Else
'in Excel 2007 (or later)
LastRowWithAName = _
Range(ColumnWithNames & Rows.CountLarge).End(xlUp).Row
End If

'this presumes things are as you described:
'two rows per person, 1st row has address info
'2nd row has phone # that needs to be moved and then deleted
For rowNumber = firstRowWithAName To LastRowWithAName Step 2
'do the copy
Range(newPhoneColumn & rowNumber) = _
Range(oldPhoneColumn & rowNumber + 1)
Range(oldPhoneColumn & rowNumber + 1).EntireRow.Delete
rowNumber = rowNumber - 1 ' adjust for deleted row!
Next
End Sub


:

I have a spreadsheet with records of persons with their address and other
information and records of the same persons with their phone numbers. The
rows are sorted by name so that the row with address and the row with phone
number are adjacent. What I need to do is merge the phone number onto the
row with the address and other data and then delete the row with the name and
phone number. Is there a way to do that in Excel? -- Chris