I have two columns, A and B. Each column contains a list of data in
text format e.g. 01TI518A.PV . It is possible that duplicates of this
text may appear within either column. Also, the number of data values
in each column is variable as they are imported from an external
source into the worksheet. I would like to be able to combine the text
from both columns into a single list in a single column, say column C.
I would also like this column to be alphanumerically sorted. How do I
go about doing this?

Sure, I can help you with that!

Here are the steps to combine the data in columns A and B into a single column C and sort it alphabetically:

1. Select the cell where you want to start the combined list, which will be the first cell in column C.
2. Type the formula "=A1" (without the quotes) in the first cell of column C. This will copy the data from cell A1 into the first cell of column C.
3. Type the formula "=B1" (without the quotes) in the second cell of column C. This will copy the data from cell B1 into the second cell of column C.
4. Select the second cell of column C (the one with the formula "=B1") and drag the fill handle (the small square in the bottom right corner of the cell) down to the last row where you want to combine data. This will copy the formula down to all the cells in column C that have data in either column A or B.
5. Select the entire column C by clicking on the column header (the letter "C" at the top of the column).
6. Click on the "Data" tab in the ribbon at the top of the Excel window.
7. Click on the "Sort A to Z" button in the "Sort & Filter" section of the ribbon. This will sort the data in column C alphabetically.

That's it! Now you should have a combined list of data from columns A and B in column C, sorted alphabetically. Let me know if you have any questions or if there's anything else I can help you with.

Check your other post.

Tommy wrote:

I have two columns, A and B. Each column contains a list of data in
text format e.g. 01TI518A.PV . It is possible that duplicates of this
text may appear within either column. Also, the number of data values
in each column is variable as they are imported from an external
source into the worksheet. I would like to be able to combine the text
from both columns into a single list in a single column, say column C.
I would also like this column to be alphanumerically sorted. How do I
go about doing this?

--

Dave Peterson

Select all of the entries in column A, choose Edit | Copy. Click in cell C1
and use Edit | Paste to paste them into column C. Select the entries in
column B, again use Edit | Copy and go to the cell below the last entry in
column C, click it and use Edit | Paste to add those to the list.
Click the 'C' to choose all of column C and either use the A|Z icon to sort
them or use Data | Sort and when asked if you wish to expand the area, reply
[No].

At this point you have both columns in one and they're sorted. Duplicate
entries will be grouped together within the column. If you need to identify
those, you can put this formula in column D at D1:
=IF(COUNTIF(C:C,C1)1,"Duplicate","")
fill the formula to the end of your list.

Or were you wanting a macro solution?

"Tommy" wrote:

I have two columns, A and B. Each column contains a list of data in
text format e.g. 01TI518A.PV . It is possible that duplicates of this
text may appear within either column. Also, the number of data values
in each column is variable as they are imported from an external
source into the worksheet. I would like to be able to combine the text
from both columns into a single list in a single column, say column C.
I would also like this column to be alphanumerically sorted. How do I
go about doing this?

If you wanted a macro to do this, then here is one that will do it. To put
it into the workbook. Press [Alt]+[F11] to open the VB Editor, then choose
Insert | Module from the VBE menu. Copy the code below and paste it into the
code module. Close the VB Editor and to run it, choose Tools | Macro |
Macros, select the macro name and click the [Run] button. It assumes empty
columns in C and D to begin with.

Sub CombineColumns()
Dim rngToCopy As Range
Dim destRange As Range
Dim lastRow As Long

lastRow = Range("A" & Rows.Count).End(xlUp).Row
Set rngToCopy = Range("A1:A" & lastRow)
Set destRange = Range("C1:C" & lastRow)
destRange.Value = rngToCopy.Value

lastRow = Range("B" & Rows.Count).End(xlUp).Row
Set rngToCopy = Range("B1:B" & lastRow)
lastRow = Range("C" & Rows.Count).End(xlUp).Row
Set destRange = Range("C" & lastRow + 1 & ":C" & _
lastRow + rngToCopy.Rows.Count)
destRange.Value = rngToCopy.Value
Set rngToCopy = Nothing

lastRow = Range("C" & Rows.Count).End(xlUp).Row
Set destRange = Range("C1:C" & lastRow)
destRange.Sort Key1:=Range("C1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

Set destRange = Range("D1:D" & lastRow)
destRange.FormulaR1C1 = "=IF(COUNTIF(C[-1],RC[-1])1," _
& Chr$(34) & "Duplicate" & Chr$(34) & "," & _
String(2, 34) & ")"
Set destRange = Nothing
End Sub


"Tommy" wrote:

I have two columns, A and B. Each column contains a list of data in
text format e.g. 01TI518A.PV . It is possible that duplicates of this
text may appear within either column. Also, the number of data values
in each column is variable as they are imported from an external
source into the worksheet. I would like to be able to combine the text
from both columns into a single list in a single column, say column C.
I would also like this column to be alphanumerically sorted. How do I
go about doing this?

Arrrggghhhh! Not to you, Dave, but to Tommy: my responses HERE emphasize why
posting in multiple places causes wasted time and effort.

"Dave Peterson" wrote:

Check your other post.

Tommy wrote:

I have two columns, A and B. Each column contains a list of data in
text format e.g. 01TI518A.PV . It is possible that duplicates of this
text may appear within either column. Also, the number of data values
in each column is variable as they are imported from an external
source into the worksheet. I would like to be able to combine the text
from both columns into a single list in a single column, say column C.
I would also like this column to be alphanumerically sorted. How do I
go about doing this?

--

Dave Peterson