Pull Data count wise

[Abhijeet]

Hi
I have data in Column A to D i want which is highest count of Column A data first that then lower and all these data will be sort with Start date.i need macro in attach file expected result is in Column N to Q please tell me how to do this

 

[Somendra Misra]

Hi Abhijeet,

Try bellow code on your sample file and than change the ranges used in the code to suit to your original file. Do test the code on original file by making a copy of it.

Sub Macro1()

Dim lrs As Long
Dim i As long
Dim lro As Long
Dim lrh As Long


lrs = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
lro = Sheets("Sheet1").Cells(Rows.Count, 14).End(xlUp).Row + 1
lrh = Sheets("Sheet1").Cells(Rows.Count, 27).End(xlUp).Row + 1

Application.ScreenUpdating = False

Range("N2:Q" & lro).Clear
Range("AA2:AB" & lrh).Clear
Range("A2:A" & lrs).Copy Range("AA2")
Application.CutCopyMode = False
lrh = Sheets("Sheet1").Cells(Rows.Count, 27).End(xlUp).Row
ActiveSheet.Range("$AA$1:$AA$" & lrh).RemoveDuplicates Columns:=1, Header:= _
        xlYes
lrh = Sheets("Sheet1").Cells(Rows.Count, 27).End(xlUp).Row
For i = 2 To lrh
   
    Range("AB" & i) = Application.WorksheetFunction.CountIf(Range("A2:A" & lrs), Range("AA" & i))
Next i
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("AB2:AB" & lrh _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("AA2:AB" & lrh)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
lrh = Sheets("Sheet1").Cells(Rows.Count, 27).End(xlUp).Row
ko = 2
For i = 2 To lrh
    For j = 2 To lrs
        If Range("A" & j).Value = Range("AA" & i) Then
            Range(Cells(j, 1), Cells(j, 4)).Copy Range("N" & ko)
            ko = ko + 1
        End If
       
        Next j
    Next i
   
   
End Sub

Regards,

 

[Abhijeet]

Macro is not sort the data

 

[Somendra Misra]

Well it sorted the data on your sample file based on the count of column A data from highest to lowest.

Regards,

 

[Abhijeet]

Yes that is macro doing but i want Start date column also sorted with this highest count come 1st