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?