Macro for adding text from one cell to another cell

[27Jack]

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??

 

[TomPl]

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

Tom

 

[27Jack]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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!!

 

[27Jack]

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\)]

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