Most efficient way to merge address columns within a table into one column?

[Coyotex3]

Hello guys, happy New Year.

Hope you guys can help yet again.

I have a table with a separate column(E:H) for the address, city, state, and zip. I would like to ideally combine that into one column in the following format " Address, City, Sate Zip." And then deleting the old columns.

What is the best way to automate this?

 

[6StringJazzer]

Use this formula in the new column

=E1&", "&F1&", "&G1&", "&H1

Fill down the entire column
Then copy the entire column, and Paste Values back into the same column.
Now you can delete the old columns.

I don't know what you are doing but once you do this, you are combining data in a way that will make it difficult to extract it back out again. For example, it will be a pain if you ever need a list of states from your addresses.

 

[Coyotex3]

Use this formula in the new column

=E1&", "&F1&", "&G1&", "&H1

Fill down the entire column
Then copy the entire column, and Paste Values back into the same column.
Now you can delete the old columns.

I don't know what you are doing but once you do this, you are combining data in a way that will make it difficult to extract it back out again. For example, it will be a pain if you ever need a list of states from your addresses.

Thank you for the reply so here is what I am trying to accomplish is to go from this:

Book1
	A	B	C	D	E	F	G	H
1	First Name	Last Name	Address	City	State	Zip Code	Grade	Major
2	John	Doe	123 ABC Street	Manhattan	New York	10001	Freshman	Biology
3	Jane	Doe	124 ABC Street	Boston	Massachussetts	10002	Sophmore	Accounting
4	Jack	Doe	125 ABC Street	Miami	Florida	10003	Juniot	Computer Science
Sheet1

To this:

Book1
	A	B	C	D	E
1	First Name	Last Name	Column1	Grade	Major
2	John	Doe	123 ABC Street, Manhattan, New York 10001	Freshman	Biology
3	Jane	Doe	124 ABC Street, Boston, Massachussetts 10002	Sophmore	Accounting
4	Jack	Doe	125 ABC Street, Miami, Florida 10003	Juniot	Computer Science
Sheet1

I know this file has different columns needing to be combined, I'm trying to automate an export that gets dumped into excel. I'm trying to consolidate a lot of the information with this macro. Once this step is done, I will try to change it around a bit to combine other columns with similar information etc.

The export will vary in length.

 

[Coyotex3]

Sub Macro3()
    Range("Table1[[#Headers],[Additional Information]]").Select
    Selection.ListObject.ListColumns.Add Position:=9
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "Full Address"
    Range("I2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-4]&"" ,""&RC[-3]&"" ,""&RC[-2]&"" , ""&RC[-1]"
    Range("$I$2").FlashFill
End Sub

Okay. This code does what I want. Can someone please teach me how to make it stop at the end of the table? I keep getting runtime 1004 error.

 

[alansidman]

Option Explicit

Sub Coyote()
    Dim lr As Long, i As Long
    Application.ScreenUpdating = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("C1").EntireColumn.Insert
    Range("C1") = "Column1"
    For i = 2 To lr
        Range("C" & i) = Range("D" & i).Value2 & ", " & Range("E" & i).Value2 & ", " & _
                         Range("F" & i).Value2 & " " & Range("G" & i).Value2
    Next i
    Range("D1:G1").EntireColumn.Delete
    Application.ScreenUpdating = True
    MsgBox "completed"
End Sub

 

[Coyotex3]

Option Explicit

Sub Coyote()
    Dim lr As Long, i As Long
    Application.ScreenUpdating = False
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("C1").EntireColumn.Insert
    Range("C1") = "Column1"
    For i = 2 To lr
        Range("C" & i) = Range("D" & i).Value2 & ", " & Range("E" & i).Value2 & ", " & _
                         Range("F" & i).Value2 & " " & Range("G" & i).Value2
    Next i
    Range("D1:G1").EntireColumn.Delete
    Application.ScreenUpdating = True
    MsgBox "completed"
End Sub

Thank you this code does absolutely what I need it to do.

Any chance you could tell me what I can do to this sub to make it stop at the end of the table?

Sub Macro3()
    Range("Table1[[#Headers],[Additional Information]]").Select
    Selection.ListObject.ListColumns.Add Position:=9
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "Full Address"
    Range("I2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-4]&"" ,""&RC[-3]&"" ,""&RC[-2]&"" , ""&RC[-1]"
    Range("$I$2").FlashFill
End Sub