Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Text if Value of Cell = 0 | Excel Worksheet Functions | |||
Adding text to cell | Excel Worksheet Functions | |||
Adding text to a cell | Excel Programming | |||
adding text to end of cell | Excel Programming | |||
Adding text to a cell from another cell | Excel Programming |