Combining data from two columns

[dziw]

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.

 

[PCLIVE]

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]

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 ne
longer column combining all the names from each column

 

[Guest]

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

 

[PCLIVE]

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]

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

PCLIVE-- How do I use that code

 

[dziw]

Bump for help with the code or other suggestions

 

[Guest]

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]

Thanks alot. It works.

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

 

[PCLIVE]

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

 

[Guest]

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

 

[PCLIVE]

Oops! There was wrap-around on that. Try this code

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]

EDIT: ok, saw your update...it works. Thanks again.

One, last question.

Let's say I have a series of sheets and Sheet2C refers to Sheet1A &
Sheet2B, Sheet3C refers to Sheet2A & Sheet3C. Is there anyway to make
it a continuous macro always referring back the the previous sheet for
column A, or would I have to repaste a modified code for each new sheet
added?

Thanks for all the help.

 

[dziw]

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.

 

[dziw]

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?