jerseyboy,
Welcome to the MrExcel forum.
1. What version of Excel and Windows are you using?
2. Are you using a PC or a Mac?
Sample raw data:
Excel 2007
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"][/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]789[/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]623[/TD]
[TD="align: right"][/TD]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
</tbody>
Sheet1
After the macro:
Excel 2007
<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]123[/TD]
[TD="align: center"]2[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]789[/TD]
[TD="align: right"]325[/TD]
[TD="align: center"]3[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]623[/TD]
[TD="align: right"]456[/TD]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]623[/TD]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]789[/TD]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
</tbody>
Sheet1
Code:
Sub GetUniques()
' hiker95, 06/20/2014, ME785906
Dim lr As Long, rng As Range, c As Range, v() As Variant
lr = Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
Set rng = Range("A1:B" & lr)
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each c In rng
If c <> "" Then
If Not .Exists(c.Value) Then
.Add c.Value, c.Value
End If
End If
Next c
v = Application.Transpose(Array(.Keys))
End With
Range("C1").Resize(UBound(v)) = v
Range("C1:C" & UBound(v, 1)).Sort key1:=Range("C1"), order1:=1
End Sub
You may have to add the
Microsoft Scripting Runtime to the
References - VBA Project.
With your workbook that contains the above:
Press the keys
ALT +
F11 to open the Visual Basic Editor
In the VBA Editor, click on:
Tools
References...
Put a checkmark in the box marked
Microsoft Scripting Runtime
Then click on the
OK button.
And, exit out of the VBA Editor.
Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension
.xlsm
Then run the
GetUniques macro.