Dynamic sorting Largest to Smallest

[Jeyakumar]

Hello team,

I have an excel sheet with raw data of exits of employees. I want to make dynamic summary of exit report like below with sorting of largest to smallest count exit reason wise (automatically every month, so excel should ignore the exit reason not available in the next month and the highest count of exit reason should come first every month)

Jan'23 report

Exit reason count

1. Better prospects 10
2. Personal reasons 8
3. Performance issue 1


Feb-23 report

Exit reason count

1. Starting own business 11
2. Performance issue 2
3. Personal reasons 1

 

[awoohaw]

Would you be kind enough to help the forum help you and post some mocked up raw data using the xl2bb add in? There is a link below.

 

[JamesCanale]

365 I hope.

MrExcelPlayground16.xlsx
	A	B	C	D	E
1	Name	Exit Date	Reason		1/1/2023
2	John Smith 1	1/1/2023	Fired for Incompentence		Better Job - 8
3	John Smith 2	1/2/2023	Smells		Own Business - 5
4	John Smith 3	1/3/2023	Retired		Quiet Quit - 5
5	John Smith 4	1/4/2023	Better Job		Smells - 5
6	John Smith 5	1/5/2023	Own Business		Fired for Incompentence - 4
7	John Smith 6	1/6/2023	Smells		Retired - 3
8	John Smith 7	1/7/2023	Retired		Complete Jerk - 1
9	John Smith 8	1/8/2023	Better Job
10	John Smith 9	1/9/2023	Own Business
11	John Smith 10	1/10/2023	Quiet Quit
12	John Smith 11	1/11/2023	Fired for Incompentence
13	John Smith 12	1/12/2023	Smells
14	John Smith 13	1/13/2023	Better Job
15	John Smith 14	1/14/2023	Better Job
16	John Smith 15	1/15/2023	Own Business
17	John Smith 16	1/16/2023	Quiet Quit
18	John Smith 17	1/17/2023	Fired for Incompentence
19	John Smith 18	1/18/2023	Smells
20	John Smith 19	1/19/2023	Complete Jerk
21	John Smith 20	1/20/2023	Better Job
22	John Smith 21	1/21/2023	Own Business
23	John Smith 22	1/22/2023	Quiet Quit
24	John Smith 23	1/23/2023	Fired for Incompentence
25	John Smith 24	1/24/2023	Smells
26	John Smith 25	1/25/2023	Quiet Quit
27	John Smith 26	1/26/2023	Better Job
28	John Smith 27	1/27/2023	Better Job
29	John Smith 28	1/28/2023	Retired
30	John Smith 29	1/29/2023	Better Job
31	John Smith 30	1/30/2023	Own Business
32	John Smith 31	1/31/2023	Quiet Quit
33	John Smith 32	2/1/2023	Fired for Incompentence
34	John Smith 33	2/2/2023	Smells
35	John Smith 34	2/3/2023	Retired
36	John Smith 35	2/4/2023	Better Job
37	John Smith 36	2/5/2023	Own Business
38	John Smith 37	2/6/2023	Quiet Quit
39	John Smith 38	2/7/2023	Fired for Incompentence
40	John Smith 39	2/8/2023	Smells
41	John Smith 40	2/9/2023	Retired
42	John Smith 41	2/10/2023	Better Job
43	John Smith 42	2/11/2023	Own Business
44	John Smith 43	2/12/2023	Quiet Quit
45	John Smith 44	2/13/2023	Smells
46	John Smith 45	2/14/2023	Retired
47	John Smith 46	2/15/2023	Better Job
48	John Smith 47	2/16/2023	Quiet Quit
49	John Smith 48	2/17/2023	Fired for Incompentence
50	John Smith 49	2/18/2023	Smells
51	John Smith 50	2/19/2023	Retired
52	John Smith 51	2/20/2023	Better Job
53	John Smith 52	2/21/2023	Own Business
54	John Smith 53	2/22/2023	Quiet Quit
55	John Smith 54	2/23/2023	Fired for Incompentence
56	John Smith 55	2/24/2023	Smells
57	John Smith 56	2/25/2023	Retired
58	John Smith 57	2/26/2023	Better Job
59	John Smith 58	2/27/2023	Better Job
60	John Smith 59	2/28/2023	Own Business
61	John Smith 60	3/1/2023	Quiet Quit
62	John Smith 61	3/2/2023	Fired for Incompentence
63	John Smith 62	3/3/2023	Smells
64	John Smith 63	3/4/2023	Retired
Sheet6

Cell Formulas
Range	Formula
E2:E8	E2	=LET(a,SORT(FILTER(C2:C64,(B2:B64>=E1)*(B2:B64<=EOMONTH(E1,0)))),b,UNIQUE(a),c,IFERROR(SEQUENCE(ROWS(a))*(--((INDEX(a,1+SEQUENCE(ROWS(a)))=INDEX(a,SEQUENCE(ROWS(a)))))),0),d,SEQUENCE(ROWS(a)),e,FILTER(d,c=0),f,INDEX(e,1+SEQUENCE(ROWS(e)))-INDEX(e,SEQUENCE(ROWS(e))),g,FILTER(f,NOT(ISERR(f))),h,SEQUENCE(ROWS(g)+1,1,0,1),i,IF(h=0,INDEX(e,1),INDEX(g,h)),j,b&" - "&i,SORTBY(j,i,-1))
Dynamic array formulas.

 

[awoohaw]

What version of Excel do you have?

 

[Fluff]

What version of Excel are you using?

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’)