Combining data from two columns

If I have two columns which are lists of names, how do I make a new column which merges the names from each column into one list, and will refresh when new data is added to the two originals.

Try:

=A1 & A2
or
=A1 & " " & A2 (if you want a space between the two text
items...)

or
=CONCATENATE(A1 & B1)

=CONCATENATE(A1," ",B1) (if you want a space between the two text
items...)


"dziw" <[email protected]> wrote in message
news:[email protected]...
>
> If I have two columns which are lists of names, how do I make a new
> column which merges the names from each column into one list, and will
> refresh when new data is added to the two originals.
>
>
> --
> dziw
> ------------------------------------------------------------------------
> dziw's Profile:
> http://www.excelforum.com/member.php...o&userid=10422
> View this thread: http://www.excelforum.com/showthread...hreadid=567563
>

Thanks, but I wasn't clear. Each column is a separate category of names, so I'm not combining each cell (i.e. first & last), but am making a new longer column combining all the names from each column.

Here's some code you might be able to use.



Range("A1:" & Range("A65536").End(xlUp).Address).Select
Selection.Copy

Range("C1").Select
ActiveSheet.Paste

Range("B1:" & Range("B65536").End(xlUp).Address).Select
Selection.Copy

Range("C65536").End(xlUp).Select
ActiveSheet.Paste

'If you want to remove duplicate names or entries, use this code
' This function must sort first.
Range("C1:" & (Range("C65536").End(xlUp).Address)).Select
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1
If Cells(RowNdx, ColNum).Value = Cells(RowNdx - 1, ColNum).Value
Then
Cells(RowNdx, ColNum).Value = ""
End If
Next RowNdx

Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'End Remove Duplicates



If you want it to update when the worksheet is changed, right-click on that
worksheet and select View code.
Click General dropdown and select Worksheet. Click other dropdown and
select Change. Then enter the code above.

HTH,
Paul





"dziw" <[email protected]> wrote in message
news:[email protected]...
>
> Thanks, but I wasn't clear. Each column is a separate category of names,
> so I'm not combining each cell (i.e. first & last), but am making a new
> longer column combining all the names from each column.
>
>
> --
> dziw
> ------------------------------------------------------------------------
> dziw's Profile:
> http://www.excelforum.com/member.php...o&userid=10422
> View this thread: http://www.excelforum.com/showthread...hreadid=567563
>

Assuming names start in A1 and B1, in C1 put this formula and copy
down.........

=A1&" "&B1

or

=B1&" "&A1

hth
Vaya con Dios
Chuck, CABGx3



"dziw" wrote:

>
> If I have two columns which are lists of names, how do I make a new
> column which merges the names from each column into one list, and will
> refresh when new data is added to the two originals.
>
>
> --
> dziw
> ------------------------------------------------------------------------
> dziw's Profile: http://www.excelforum.com/member.php...o&userid=10422
> View this thread: http://www.excelforum.com/showthread...hreadid=567563
>
>

Thanks CLR, but I'm not trying to combine each cell together. I'm trying to make a new column (C) which includes each name from two other columns (A & B) and refeshes when A or B is updated with new names.

PCLIVE-- How do I use that code?

Bump for help with the code or other suggestions

Here's a little mod of PCLIVE's code......

Sub CombineColumns()
'This macro will clear the contents of column C, then
'copy the values from column A to column C, and then
'append the values in column B to the bottom of that list in column C
Range("c:c").ClearContents
Range("A1:" & Range("A65536").End(xlUp).Address).Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Range("B1:" & Range("B65536").End(xlUp).Address).Select
Selection.Copy
Range("C65536").End(xlUp).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste
End Sub

Vaya con Dios,
Chuck, CABGx3



"dziw" wrote:

>
> Bump for help with the code or other suggestions
>
>
> --
> dziw
> ------------------------------------------------------------------------
> dziw's Profile: http://www.excelforum.com/member.php...o&userid=10422
> View this thread: http://www.excelforum.com/showthread...hreadid=567563
>
>

Thanks alot. It works.

I'm not really familiar with coding, so how would I change the references if I'm referring to column A in another worksheet.

Add "Worksheets("SheetName")." to the beginning of the Range References
and change "Select" for those ones you want to copy, to "Copy". The
modified code below will copy the specified range from the sheet you
specify. Please change "SheetName" to the name of the sheet you want to
copy from. It then pastes to the active sheet. Give it a try.


Sub CombineColumns()
'This macro will clear the contents of column C, then
'copy the values from column A to column C, and then
'append the values in column B to the bottom of that list in column C
Range("c:c").ClearContents
Worksheets("SheetName").Range("A1:" &
Worksheets("SheetName").Range("A65536").End(xlUp).Address).Copy
Range("C1").Select
ActiveSheet.Paste
Worksheets("SheetName").Range("B1:" &
Worksheets("SheetName").Range("B65536").End(xlUp).Address).Copy
Range("C65536").End(xlUp).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste
End Sub



"dziw" <[email protected]> wrote in message
news:[email protected]...
>
> Thanks alot. It works.
>
> I'm not really familiar with coding, so how would I change the
> references if I'm referring to column A in another worksheet.
>
>
> --
> dziw
> ------------------------------------------------------------------------
> dziw's Profile:
> http://www.excelforum.com/member.php...o&userid=10422
> View this thread: http://www.excelforum.com/showthread...hreadid=567563
>

This will get the data from column A on Sheet2 and column B on Sheet3 and
paste it all in column C on sheet1...........

Sub CombineColumns()
'This macro will clear the contents of column C, then
'copy the values from column A to column C, and then
'append the values in column B to the bottom of that list in column C
Worksheets("sheet1").Select 'The sheet where you want the results
Range("c:c").ClearContents
Worksheets("sheet2").Select 'The sheet where column A data is
Range("A1:" & Range("A65536").End(xlUp).Address).Select
Selection.Copy
Worksheets("sheet1").Select 'Go back to paste the data
Range("C1").Select
ActiveSheet.Paste
Worksheets("sheet3").Select 'the sheet where column B data is
Range("B1:" & Range("B65536").End(xlUp).Address).Select
Selection.Copy
Worksheets("sheet1").Select 'go back to paste the data
Range("C65536").End(xlUp).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste
Range("c1").Select
End Sub

hth
Vaya con Dios,
Chuck, CABGx3




"dziw" wrote:

>
> Thanks alot. It works.
>
> I'm not really familiar with coding, so how would I change the
> references if I'm referring to column A in another worksheet.
>
>
> --
> dziw
> ------------------------------------------------------------------------
> dziw's Profile: http://www.excelforum.com/member.php...o&userid=10422
> View this thread: http://www.excelforum.com/showthread...hreadid=567563
>
>

Oops! There was wrap-around on that. Try this code
"PCLIVE" <[email protected]> wrote in message
news:%23mMI4Q%[email protected]...
> Add "Worksheets("SheetName")." to the beginning of the Range References
> and change "Select" for those ones you want to copy, to "Copy". The
> modified code below will copy the specified range from the sheet you
> specify. Please change "SheetName" to the name of the sheet you want to
> copy from. It then pastes to the active sheet. Give it a try.
>
>
Sub CombineColumns()
'This macro will clear the contents of column C, then
'copy the values from column A to column C, and then
'append the values in column B to the bottom of that list in column C

Range("c:c").ClearContents
Worksheets("SheetName").Range("A1:" & _
Worksheets("SheetName").Range("A65536").End(xlUp).Address).Copy
Range("C1").Select
ActiveSheet.Paste
Worksheets("SheetName").Range("B1:" & _
Worksheets("SheetName").Range("B65536").End(xlUp).Address).Copy
Range("C65536").End(xlUp).Select
Selection.Offset(1, 0).Select
ActiveSheet.Paste
End Sub
>
>
>
> "dziw" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Thanks alot. It works.
>>
>> I'm not really familiar with coding, so how would I change the
>> references if I'm referring to column A in another worksheet.
>>
>>
>> --
>> dziw
>> ------------------------------------------------------------------------
>> dziw's Profile:
>> http://www.excelforum.com/member.php...o&userid=10422
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=567563
>>
>
>

Bump for help with referencing previous/current sheets.

Basically, I have a series of months a worksheets, with categories of names in columns. Each month the names shift to different categories, and one category combines names.

I want to see if I have to write one long code with each month, or a short code that refers to the previous and current worksheet.

Having a problem with the code. It was working when copying cells with the names in the actual cell on a sample worksheet.

But, on the actual worksheet, the names in the cells are references to cells from previous month worksheets. When I run the code like that, it doesn't work properly.

Any way to change the code?

Or you can simply use the DigDB plugin - its much easier and faster.

Details: Append data to column in Excel using DigDB

Hi, try this combining cells in Excel. You can combine multiple columns into one cell regardless of if you are using formulas or not.