Forum Discussion
How to find top 3 from each category using Excel dynamic formula?
I have many data with ranking percentage. Here is few of a sample. I want to find top3 of ranking from each category by a single cell dynamic spill formula.
5 Replies
By the way, ChatGPT recommended the following solution :
=LET(
srcNames, $A$2:$A$20,
srcVals, $B$2:$B$20,
u, UNIQUE(srcNames),
n, ROWS(u),
repNames, SORT(INDEX(u, MOD(SEQUENCE(n*3)-1, n)+1)),
ranks, MOD(SEQUENCE(n*3)-1, 3)+1,
percs, MAP(
repNames, ranks,
LAMBDA(nm,k,
LARGE(
FILTER(srcVals, srcNames = nm),
k
)
)
),
HSTACK(repNames, percs)
)
This is one formula that provides your desired output.It might not be exactly as you requested, but I kept the solution in two steps to keep it simple.
Social Media =SORT(LET(
u, UNIQUE($A$2:$A$20),
n, ROWS(u),
INDEX(u, MOD(SEQUENCE(n*3)-1, n)+1)
))Percent =LET(
nm,H2,
k, COUNTIF( H2:$H$2, nm),
LARGE(FILTER($B$2:$B$20, $A$2:$A$20=nm), k)
)One possibility, using the same basic concept of my INSTANCENUM function:
=LET( arr, SORT(A2:B20, {1,2}, {1,-1}), key, TAKE(arr,, 1), FILTER(arr, 4 > SCAN(0, key = DROP(VSTACK("", key), -1), LAMBDA(a,v, 1 + a * v))) )Or, as a curried LAMBDA function:
=LAMBDA(arr,FILTER(arr,4>LAMBDA(key,SCAN(0,key=DROP(VSTACK("",key),-1),LAMBDA(a,v,1+a*v)))(TAKE(arr,,1))))(SORT(A2:B20,{1,2},{1,-1}))p.s. I think everyone is currently having issues with file attachments being removed from their posts.
Good one. Your formula works on my sample data. Let me apply it to a large dataset and measure performance.
Strange, I have attached a sample file in my post but it missing. Here is a download link of a sample file.
