Macro for adding text from one cell to another cell
 

I would like to make a macro to help me go through 10,000 rows of an excel
spreadsheet.

I have columns set-up with last_name, first_name, address (etc). I would
like to take the first name from one row and add it into the first_name cell
of another row.

Example

John | Doe
Jane | Doe

What I would like to have is:

John & Jane | Doe

Any help??

Macro for adding text from one cell to another cell
 

How would you decide which rows to copy the name from and to?

Tom

"27Jack" wrote:

I would like to make a macro to help me go through 10,000 rows of an excel
spreadsheet.

I have columns set-up with last_name, first_name, address (etc). I would
like to take the first name from one row and add it into the first_name cell
of another row.

Example

John | Doe
Jane | Doe

What I would like to have is:

John & Jane | Doe

Any help??

Macro for adding text from one cell to another cell
 

The rows are next to each other.

This is a database of voters. Each row is an individual. I want to send a
mailing to households. So, I have sorted the database by address, then by
last name. I visually decide if I want the two names to combine, and then
delete the row I took the one name from. That way I have a database by
household.

I am doing this manually, and have covered just about 3000 rows... still
have 7000 to go... would love to find an easier way.

Thanks

"TomPl" wrote:

How would you decide which rows to copy the name from and to?

Tom

"27Jack" wrote:

I would like to make a macro to help me go through 10,000 rows of an excel
spreadsheet.

I have columns set-up with last_name, first_name, address (etc). I would
like to take the first name from one row and add it into the first_name cell
of another row.

Example

John | Doe
Jane | Doe

What I would like to have is:

John & Jane | Doe

Any help??

Macro for adding text from one cell to another cell
 

I think this macro will do what you want (see the Note at the end though)...

Sub CombineLikeNames()
Dim X As Long
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For X = LastRow To 2 Step -1
If .Cells(X, "C").Value & .Cells(X, "F").Value = _
.Cells(X - 1, "C").Value & .Cells(X - 1, "F").Value Then
.Cells(X - 1, "A").Value = .Cells(X - 1, "A").Value & _
" & " & .Cells(X, "A").Value
.Cells(X, "A").EntireRow.Delete
End If
Next
End With
End Sub

Note: I was unsure of your column assignments (you said the order was Last
Name, First Name but your example showed First Name, Last Name ordering), so
I assumed Column A contained the First Name, Column B contained the Last
Name and Column C contained the address. Also, in order to insure the same
address in two different towns in the same state didn't screw things up if
they sorted next to each other, I concatenated the zip code for each record
onto the address in the check being performed in the first If..Then
statement. Since you didn't provide the zip code information, I assumed it
was in Column "F". Obviously, if either of these assumptions (Column A for
First Name, Column F for zip codes) is incorrect, you will have to
substitute the correct column letters into the above macro before you run
it. Also, I would suggest you test the macro out on a copy of your data
first.<g

Rick


"27Jack" wrote in message
...
The rows are next to each other.

This is a database of voters. Each row is an individual. I want to send
a
mailing to households. So, I have sorted the database by address, then by
last name. I visually decide if I want the two names to combine, and then
delete the row I took the one name from. That way I have a database by
household.

I am doing this manually, and have covered just about 3000 rows... still
have 7000 to go... would love to find an easier way.

Thanks

"TomPl" wrote:

How would you decide which rows to copy the name from and to?

Tom

"27Jack" wrote:

I would like to make a macro to help me go through 10,000 rows of an
excel
spreadsheet.

I have columns set-up with last_name, first_name, address (etc). I
would
like to take the first name from one row and add it into the first_name
cell
of another row.

Example

John | Doe
Jane | Doe

What I would like to have is:

John & Jane | Doe

Any help??

Macro for adding text from one cell to another cell
 

Rick,

Actually, the spreadsheet is very large...

Last Name is column D
First Name is column E
Zip Codes are in column O
Street address is in column K

Does that make a difference, or can I just substitute?

Thank you!!

"Rick Rothstein (MVP - VB)" wrote:

I think this macro will do what you want (see the Note at the end though)...

Sub CombineLikeNames()
Dim X As Long
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For X = LastRow To 2 Step -1
If .Cells(X, "C").Value & .Cells(X, "F").Value = _
.Cells(X - 1, "C").Value & .Cells(X - 1, "F").Value Then
.Cells(X - 1, "A").Value = .Cells(X - 1, "A").Value & _
" & " & .Cells(X, "A").Value
.Cells(X, "A").EntireRow.Delete
End If
Next
End With
End Sub

Note: I was unsure of your column assignments (you said the order was Last
Name, First Name but your example showed First Name, Last Name ordering), so
I assumed Column A contained the First Name, Column B contained the Last
Name and Column C contained the address. Also, in order to insure the same
address in two different towns in the same state didn't screw things up if
they sorted next to each other, I concatenated the zip code for each record
onto the address in the check being performed in the first If..Then
statement. Since you didn't provide the zip code information, I assumed it
was in Column "F". Obviously, if either of these assumptions (Column A for
First Name, Column F for zip codes) is incorrect, you will have to
substitute the correct column letters into the above macro before you run
it. Also, I would suggest you test the macro out on a copy of your data
first.<g

Rick


"27Jack" wrote in message
...
The rows are next to each other.

This is a database of voters. Each row is an individual. I want to send
a
mailing to households. So, I have sorted the database by address, then by
last name. I visually decide if I want the two names to combine, and then
delete the row I took the one name from. That way I have a database by
household.

I am doing this manually, and have covered just about 3000 rows... still
have 7000 to go... would love to find an easier way.

Thanks

"TomPl" wrote:

How would you decide which rows to copy the name from and to?

Tom

"27Jack" wrote:

I would like to make a macro to help me go through 10,000 rows of an
excel
spreadsheet.

I have columns set-up with last_name, first_name, address (etc). I
would
like to take the first name from one row and add it into the first_name
cell
of another row.

Example

John | Doe
Jane | Doe

What I would like to have is:

John & Jane | Doe

Any help??

Macro for adding text from one cell to another cell
 

Rick,

Okay... this macro worked until I got to Apartments which is Column L.

So both column K and L have to be compared.

Oh my gosh... this is great... even if I have to take apartment out... I
have spent two day just comparing, pasting and typing, what a time saver!

Thank you!

"Rick Rothstein (MVP - VB)" wrote:

I think this macro will do what you want (see the Note at the end though)...

Sub CombineLikeNames()
Dim X As Long
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For X = LastRow To 2 Step -1
If .Cells(X, "C").Value & .Cells(X, "F").Value = _
.Cells(X - 1, "C").Value & .Cells(X - 1, "F").Value Then
.Cells(X - 1, "A").Value = .Cells(X - 1, "A").Value & _
" & " & .Cells(X, "A").Value
.Cells(X, "A").EntireRow.Delete
End If
Next
End With
End Sub

Note: I was unsure of your column assignments (you said the order was Last
Name, First Name but your example showed First Name, Last Name ordering), so
I assumed Column A contained the First Name, Column B contained the Last
Name and Column C contained the address. Also, in order to insure the same
address in two different towns in the same state didn't screw things up if
they sorted next to each other, I concatenated the zip code for each record
onto the address in the check being performed in the first If..Then
statement. Since you didn't provide the zip code information, I assumed it
was in Column "F". Obviously, if either of these assumptions (Column A for
First Name, Column F for zip codes) is incorrect, you will have to
substitute the correct column letters into the above macro before you run
it. Also, I would suggest you test the macro out on a copy of your data
first.<g

Rick


"27Jack" wrote in message
...
The rows are next to each other.

This is a database of voters. Each row is an individual. I want to send
a
mailing to households. So, I have sorted the database by address, then by
last name. I visually decide if I want the two names to combine, and then
delete the row I took the one name from. That way I have a database by
household.

I am doing this manually, and have covered just about 3000 rows... still
have 7000 to go... would love to find an easier way.

Thanks

"TomPl" wrote:

How would you decide which rows to copy the name from and to?

Tom

"27Jack" wrote:

I would like to make a macro to help me go through 10,000 rows of an
excel
spreadsheet.

I have columns set-up with last_name, first_name, address (etc). I
would
like to take the first name from one row and add it into the first_name
cell
of another row.

Example

John | Doe
Jane | Doe

What I would like to have is:

John & Jane | Doe

Any help??

Macro for adding text from one cell to another cell
 

Just concatenating the Column L values with the address should be enough to
handle that. Give this a try (again, on a copy of your data)...

Sub CombineLikeNames()
Dim X As Long
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "E").End(xlUp).Row
For X = LastRow To 2 Step -1
If .Cells(X, "K").Value & .Cells(X, "O").Value & _
.Cells(X, "L").Value = .Cells(X - 1, "K").Value & _
.Cells(X - 1, "O").Value & .Cells(X, "L").Value Then
.Cells(X - 1, "E").Value = .Cells(X - 1, "E").Value & _
" & " & .Cells(X, "E").Value
.Cells(X, "E").EntireRow.Delete
End If
Next
End With
End Sub

I believe I correctly accounted for the column references that you posted.

Rick


"27Jack" wrote in message
...
Rick,

Okay... this macro worked until I got to Apartments which is Column L.

So both column K and L have to be compared.

Oh my gosh... this is great... even if I have to take apartment out... I
have spent two day just comparing, pasting and typing, what a time saver!

Thank you!

"Rick Rothstein (MVP - VB)" wrote:

I think this macro will do what you want (see the Note at the end
though)...

Sub CombineLikeNames()
Dim X As Long
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For X = LastRow To 2 Step -1
If .Cells(X, "C").Value & .Cells(X, "F").Value = _
.Cells(X - 1, "C").Value & .Cells(X - 1, "F").Value Then
.Cells(X - 1, "A").Value = .Cells(X - 1, "A").Value & _
" & " & .Cells(X, "A").Value
.Cells(X, "A").EntireRow.Delete
End If
Next
End With
End Sub

Note: I was unsure of your column assignments (you said the order was
Last
Name, First Name but your example showed First Name, Last Name ordering),
so
I assumed Column A contained the First Name, Column B contained the Last
Name and Column C contained the address. Also, in order to insure the
same
address in two different towns in the same state didn't screw things up
if
they sorted next to each other, I concatenated the zip code for each
record
onto the address in the check being performed in the first If..Then
statement. Since you didn't provide the zip code information, I assumed
it
was in Column "F". Obviously, if either of these assumptions (Column A
for
First Name, Column F for zip codes) is incorrect, you will have to
substitute the correct column letters into the above macro before you run
it. Also, I would suggest you test the macro out on a copy of your data
first.<g

Rick


"27Jack" wrote in message
...
The rows are next to each other.

This is a database of voters. Each row is an individual. I want to
send
a
mailing to households. So, I have sorted the database by address, then
by
last name. I visually decide if I want the two names to combine, and
then
delete the row I took the one name from. That way I have a database by
household.

I am doing this manually, and have covered just about 3000 rows...
still
have 7000 to go... would love to find an easier way.

Thanks

"TomPl" wrote:

How would you decide which rows to copy the name from and to?

Tom

"27Jack" wrote:

I would like to make a macro to help me go through 10,000 rows of an
excel
spreadsheet.

I have columns set-up with last_name, first_name, address (etc). I
would
like to take the first name from one row and add it into the
first_name
cell
of another row.

Example

John | Doe
Jane | Doe

What I would like to have is:

John & Jane | Doe

Any help??