Unique Values

[mgm05267]

Hi...

I have 2 Columns (A & B) like below:

A B
A1 B1
A1 B2
A2 B3
A4 B2
A1 B3
A2 B2
A5 B1

What I am looking for is like below:

A B
A1 B1
A1 B2
A1 B3
A2 B2
A2 B3
A4 B2
A5 B1

Can anyone please help...

Regards,

MGM

[mikerickson]

One sorting (column A then column B) should do what you want.

This is the equivalent of
sort on column B then do anohter sort on column A.

[mgm05267]

Hi mikerickson,

Thanks for reply....

But here there will be more than 6000 entries which will have duplicates

Also, A1 B1 is one unique row & A1 B2 is another unique. But there should not be another A1 B1 in the range.

Please help...

Regards,

MGM

[mdmackillop]

Sub ClearDupes()

Dim oset As Long
Dim r As Range

oset = 7 '<=== adjust to suit

Set r = Intersect(ActiveSheet.UsedRange, Columns(1))
With r.Offset(1)
Cells(1, oset + 1).Resize(, 2) = Array("Data", "Count")
.Offset(, oset).FormulaR1C1 = "=R[]C1 & R[]C2"
.Offset(, oset + 1).FormulaR1C1 = "=COUNTIF(R1C[-1]:R[]C[-1],R[]C[-1])"

r.Offset(, oset + 1).AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria1:=">1"
.Offset(, oset + 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
.Offset(, oset).Resize(, 2).EntireColumn.Clear
End With
Call DoSort

End Sub


Sub DoSort()
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A:A"), _
SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add Key:=Range("B:B"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A:B")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub