mgm05267
01-02-2012, 11:04 AM
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
01-02-2012, 11:10 AM
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
01-02-2012, 11:15 AM
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
01-02-2012, 02:20 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.