Single list from multiple columns

[Ranger21]

I have a worksheet with multiple columns, anywhere from 2 to 10 or more. These columns are lists of numbers (UPC codes, actually). The columns are of different lengths. Some numbers are common to all columns and some are common to only a few and some are unique to one column. What I need to do is combine the columns into a single list of numbers with no duplications. This will be a grocery store chain authorization list. I do this manually now. I would appreciate any help.

[Dave Hawley]

Hi Ranger

The quickest way would be via Excels Advanced Filter found under data>Filter. This means you would need to place all Columns into a single Column then go there and set it for Unique values only.

[Dave Hawley]

Hi Ranger


Here is a VBA method. You MUST have Columns "A" and "B" empty. You then just select each ENTIRE Column containing you data by using holding down the Ctrl key

Sub ExtractUniques()
Dim i As Integer
Dim rRange As Range
'Written By Dave Hawley of http://www.OzGrid.com
Range("A1") = "Unique List"
Range("A1").Font.Bold = True
Set rRange = Selection
For i = 1 To rRange.Areas.Count
With rRange.Areas(i).Columns(1)
Range(.Cells(1, 1), .Cells(65536, 1).End(xlUp)).Copy _
Destination:=Range("A65536").End(xlUp).Offset(1, 0)
End With
Next

Range("A1", Range("A65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=Range("B1"), unique:=True
Columns(1).Delete
End Sub