Tom’s Tutorials for Excel: Unique List From Multi-Column Table

Tom’s Tutorials For Excel: Unique List From Multi-Column Table
You may know that from a single-column list containing repeated items, you can extract a list of unique items using Advanced Filter. But what if you want to extract a unique list from a table having many columns of repeatedly listed items?

In the picture, a fictional quarterly survey keeps a list of top 10 vacation destinations. Many of those destinations are repeated among the four quarterly columns. The following macro lists all vacation destinations that are in the table, making sure that each is named only once in column G.

Sub UniqueList()
'Turn off screen updating
Application.ScreenUpdating = False

'Declare and define variables
Dim cell As Range, TableRange As Range
Dim xRow As Long, varCell As Variant
Set TableRange = Range("B4:E13")
xRow = 2

'Clear column G (column #7) where the list will go.
Columns(7).Clear
'Enter the header label in cell G1 and bold cell G1.
With Range("G1")
.Value = "Unique list:"
.Font.Bold = True
End With

'Loop through each cell in the table range,
'and add that cell's value to the list if it
'does not exist in the list yet.
For Each cell In TableRange
varCell = Application.Match(cell.Value, Columns(7), 0)
If IsError(varCell) Then
Err.Clear
Cells(xRow, 7).Value = cell.Value
xRow = xRow + 1
End If
Next cell

'Clear the TableRange object variable from system memory.
Set TableRange = Nothing

'Sort the list in alphabetical order.
Range("G1").CurrentRegion.Sort Key1:=Range("G2"), _
Order1:=xlAscending, Header:=xlYes
'Autofit column G.
Columns(7).AutoFit

'Turn screen updating back on.
Application.ScreenUpdating = True
End Sub