Automatic Top 10 Ranking by Date Reference

Every day I run a report that identifies the reason someone contacted our team. I need to rank them daily and weekly on the top 10 reasons for contact. My data is in a format like the below example - please can I have suggestions on how to automate the ranking and sorting? I will need the reason code itself and the quantity on that date, sorted into top 10 most common.

Currently we have a list of the reason codes and are doing a VLOOKUP based on the date/week # and sorting the results manually through a macro button. This becomes a pain if you need to add or change any of the reason codes - I know there has to be an easier way but my attempts at writing code to do what I need have failed.

Thank you in advance.

You talk about the top 10 reasons... and your raw data contains only 6.

So... please a) be precise and b) populate your sheet with a few examples of WHAT you expect to see WHERE you expect so see it.

There are 23 reasons for week 1, Glenn (if I have interpreted it correctly)

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	K
3	1
4	2
5	3
6	4
7	7
8	8
9	9
10	10
11	12
12	16
13	17
14	24
15	28
16	29
17	32
18	33
19	42
20	80
21	84
22	97
23	115
24	125
25	137

Sheet: Sheet1

Ali... where on earth did you see that??

I see C3:H9 as the reasons for week 1 and C10:H16 as the reasons for week 2.

I have got this far, but I am still useless trying to do COUNTIF with a dynamic array:

=LET(f,FILTER(C3:H16,B3:B16=1),s,SORT(TOCOL(f,1,1)),u,UNIQUE(s),s)

Ahhh. No, I think C3 is the number of reasons A chosen on week 1 3rd July. We'll see, when a more complete explanation is provided

I agree - it could easily be that way around!

By date could be this:

=LET(h,C1:L1,v,VSTACK(h,FILTER(C3:L16,A3:A16=P1)),t,TRANSPOSE(SORTBY(v,INDEX(v,2,),-1)),HSTACK(SEQUENCE(ROWS(t),,1,1),t))

And by week:

=LET(h,C1:L1,f,FILTER(C3:L16,B3:B16=U1),v,VSTACK(h,BYCOL(f,LAMBDA(x,SUM(x)))),t,TRANSPOSE(SORTBY(v,INDEX(v,2),-1)),HSTACK(SEQUENCE(ROWS(t),,1,1),t))

All in one:

Please Login or Register  to view this content.

Administrative Note

... please don't ignore contributors to your thread - it takes members time to provide suggestions, so acknowledge all solutions offered, even if they don't meet your requirements. Thanks.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

and merely 'cos I got there:

=LET(A,TOCOL(C1:L1),B,TOCOL(FILTER(C3:L16,A3:A16=P1)),C,HSTACK(A,B),D,FILTER(C,INDEX(C,,2)<>" "),E,TAKE(SORTBY(D,INDEX(D,,2),-1,INDEX(D,,1),1),10),F,SEQUENCE(ROWS(E)),HSTACK(F,E))

and

=LET(A,TOCOL(C1:L1),B,TOCOL(BYCOL(FILTER(C3:L16,B3:B16=W1),LAMBDA(x,SUM(x)))),C,HSTACK(A,B),D,FILTER(C,INDEX(C,,2)<>" "),E,TAKE(SORTBY(D,INDEX(D,,2),-1,INDEX(D,,1),1),10),F,SEQUENCE(ROWS(E)),HSTACK(F,E))

Incidentally, neither of the other solutions work for 3rd July

Nor do they sort tied values alphabetically... So don't forget to check Post 15....

If you want an explanation of any of them, just ask.