List of top 10 entries between dates

[VOR]

Hello all,
I am trying to work out how to make a formula to list the top 10 names in J11 and K11 based on how many times they appear on the list in A:B
To make it even more complex, I would like to have the formula refer to the date range in J8 and J9.

Any help would be much appreciated.

Being Excel 2019 I do not have the new dynamic formulas.

 

[Muhammad_Usman]

You Can Use Pivot Table to sort out you problem.

26-08-21 Exp.xlsx
	A	B	C	D	E	F	G	H
1	Date	Name				Date	(Multiple Items)
2	8/26/2021	david
3	8/26/2021	THOMAS				Row Labels	Count of Name
4	8/26/2021	SANDRA				THOMAS	6
5	8/29/2021	ROSS				ROSS	5
6	8/29/2021	ROSS				SANDRA	4
7	8/29/2021	david				david	3
8	8/29/2021	VICTORIA				NICK	1
9	9/2/2021	THOMAS				VICTORIA	1
10	9/2/2021	NICK				Grand Total	20
11	9/2/2021	SANDRA
12	9/2/2021	SANDRA
13	9/6/2021	ROSS
14	9/6/2021	SANDRA
15	9/6/2021	ROSS
16	9/6/2021	ROSS
17	9/10/2021	THOMAS
18	9/10/2021	THOMAS
19	9/10/2021	THOMAS
20	9/13/2021	THOMAS
21	9/13/2021	david
22	9/15/2021	david
Sheet11

 

[VOR]

Thanks Muhammad_Usman, I don't think a Pivot table will update automatically or refer to the dates selected. I was looking for an overview style report which is why I was looking for a formula to be honest.
Thanks for looking though, if you have any other idea's I would welcome them.

 

[JEC]

Put this array formula in E2 (helpercolumn).

=IFERROR(INDEX($B$1:$B$26;SMALL(IF(($A$1:$A$26>=$J$8)*($A$1:$A$26<=$J$9);ROW($1:$26));ROW(A1)));"")

This in G2 (wanted output) Close both formula's with ctrl+shift+return.

=INDEX($E$1:$E$26;MAX(((LARGE(COUNTIFS($E$1:$E$26;$E$1:$E$26;$E$1:$E$26;"><")+(ROW($1:$26)/1000);SUM(COUNTIF($G$1:G1;OFFSET($E$1;;;SUMPRODUCT(--($E$1:$E$26<>"")))))+1)=COUNTIFS($E$1:$E$26;$E$1:$E$26;$E$1:$E$26;"><")+(ROW($1:$26)/1000))*ROW($1:$26))))

What about VBA? Able to use that?

 

[Fluff]

Being Excel 2019

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[JEC]

For example

Sub jec()
  ar = Sheets(1).Cells(1).CurrentRegion
  With CreateObject("scripting.dictionary")
     For i = 1 To UBound(ar)
        If ar(i, 1) >= [J8] And ar(i, 1) <= [J9] Then .Item(ar(i, 2)) = .Item(ar(i, 2)) + 1
     Next
    Sheets(1).Cells(1, 4).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
  End With
   
  With Sheets(1).Range("E1").CurrentRegion
     .Sort [E1], 2, , , , , , 0
     .Offset(5).ClearContents
  End With
End Sub

 

[alansidman]

Using Mohamed's Data here is an Mcode from Power Query. PQ will update when new data is added to the source table by clicking on the Refresh All Icon

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 

[VOR]

For example

Sub jec()
  ar = Sheets(1).Cells(1).CurrentRegion
  With CreateObject("scripting.dictionary")
     For i = 1 To UBound(ar)
        If ar(i, 1) >= [J8] And ar(i, 1) <= [J9] Then .Item(ar(i, 2)) = .Item(ar(i, 2)) + 1
     Next
    Sheets(1).Cells(1, 4).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
  End With
  
  With Sheets(1).Range("E1").CurrentRegion
     .Sort [E1], 2, , , , , , 0
     .Offset(5).ClearContents
  End With
End Sub

Thanks Jec, I'm having some trouble inputting the first formula for some reason, I didn't want you to think I was ignoring you. I will keep trying and try the VBA...

 

[JEC]

Ahh I see. You have to replace all the semi colons to a comma (in he formula ; to ,). Im using other regional pc settings.