• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Top n repeated values with criteria

Opal58

New Member
I found this wonderful formula last week in your forum that resolved an issue I was having that provided the top 3 most repeated values in a column.

= LET(
distinct, UNIQUE(zones),
count, COUNTIFS(zones, distinct),
ordered, SORTBY(distinct, count,-1),
INDEX(ordered, {1;2;3}))

Now, there is an extra hitch, I need to be able to find the top 3 most repeated values for each Model type identified in another column. Does anyone have a way that I can resolve this? Thank you.
Model TypeZone
101949
123949
123930
101822
101822
123930
123949
 
Power Query Solution. If I am understanding your needs, none of your sample have at least three repeating items.
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Model Type", "Zone"}, {{"Count", each _, type table [Model Type=number, Zone=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Model Type", "Zone", "Index"}, {"Model Type", "Zone", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [Index] < 4)
in
    #"Filtered Rows"

Excel 2016 (Windows) 64 bit
A
B
C
D
E
F
2
101​
949​
101​
949​
1​
3
123​
949​
123​
949​
1​
4
123​
930​
123​
949​
2​
5
101​
822​
123​
930​
1​
6
101​
822​
123​
930​
2​
7
123​
930​
101​
822​
1​
8
123​
949​
101​
822​
2​
Sheet: Sheet1
 
Last edited:
Thank you, unfortunately that is not the result I am trying to achieve.

I want to see the following two tables as my result:

Model 101Zone
Top 1822
Top 2949
Top 3

Model 123Zone
Top 1949
Top 2930
Top 3

I only displayed a small sampling of data, I could get up to 70 rows of data on the spreadsheet. The formula I noted above gave me all records despite the Model and I need to differentiate by model.
 
issue I was having that provided the top 3
I provided a solution based upon the above statement. You need to be clear in your needs and in the future provide a mocked up solution of what you want. Good Luck finding a solution.
 
Oh, my... I just figured it out and it's so simple!!!

= LET(
distinct, UNIQUE(FILTER(zones,ModelType = 101)),
count, COUNTIFS(zones, distinct,ModelType,101),
ordered, SORTBY(distinct, count,-1),
INDEX(ordered, {1;2;3}))
 
Last edited:
Hi @Opal58 !

You can use this option too:
PHP:
=LET(z, Tabla1[Zone],
     m, Tabla1[Model Type],
     u, UNIQUE(FILTER(z, m = 101)),
     TAKE(SORTBY(u, -COUNTIFS(z, u, m, 101)), 3)
 )
Blessings!
 
Back
Top