How to make automatic unique ranking table if changing only week number?

[Sochen]

Hi All,

I have the excel below which I want to make a dynamic table.
1. The data in the table automatically change if change the week number
2. Only top 5 categories (unique) will be appear in the table base on the sales performance

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2							Change the week number from Week 2 to Week 3 as the result will automatically change in the table below
3
4
5	Week No.	Date	Categories	Sales			Week No.	2			Week No.	3
6	2	11-Jan-23	Apple	$ 50.00			Rank.	Categories	Total Sales		Rank.	Categories	Total Sales
7	2	11-Jan-23	Mango	$ 25.00			1	Apple	$ 90.00		1	Banana	$ 80.00
8	2	11-Jan-23	Banana	$ 15.00			2	Dragon fruits	$ 55.00		2	Apple	$ 75.00
9	2	11-Jan-23	Orange	$ 10.00			3	Watermelon	$ 45.00		3	Strawberry	$ 70.00
10	2	14-Jan-23	Apple	$ 40.00			4	Banana	$ 40.00		4	Orange	$ 65.00
11	2	14-Jan-23	Mango	$ 30.00			5	Mango	$ 35.00		5	Cherry	$ 60.00
12	2	14-Jan-23	Watermelon	$ 20.00
13	2	14-Jan-23	Papaya	$ 75.00			Note:	Update the Week No. to see the sale performance of any week.
14	2	15-Jan-23	Dragon fruits	$ 15.00				Once changing the week number, then the categories and total sales will automaticallt change. And only the top 5 categories will appear on the table.
15	3	18-Jan-23	Cherry	$ 60.00
16	3	18-Jan-23	Apple	$ 75.00
17	3	18-Jan-23	Strawberry	$ 70.00
18	3	18-Jan-23	Grapes	$ 30.00
19	3	18-Jan-23	Durian	$ 20.00
20	3	18-Jan-23	Orange	$ 55.00
21	3	18-Jan-23	Banana	$ 35.00
22	3	18-Jan-23	Pineaple	$ 55.00
23	3	19-Jan-23	Orange	$ 10.00
24	3	19-Jan-23	Banana	$ 45.00
25
Sheet1

Cell Formulas
Range	Formula
A6:A24	A6	=ISOWEEKNUM(B6)

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2							Change the week number from Week 2 to Week 3 as the result will automatically change in the table below
3
4
5	Week No.	Date	Categories	Sales			Week No.	2			Week No.	3
6	2	11-Jan-23	Apple	50			Rank.	Categories	Total Sales		Rank.	Categories	Total Sales
7	2	11-Jan-23	Mango	25			1	Apple	90		1	Banana	80
8	2	11-Jan-23	Banana	15			2	Papaya	75		2	Apple	75
9	2	11-Jan-23	Orange	10			3	Mango	55		3	Strawberry	70
10	2	14-Jan-23	Apple	40			4	Watermelon	20		4	Orange	65
11	2	14-Jan-23	Mango	30			5	Banana	15		5	Cherry	60
12	2	14-Jan-23	Watermelon	20
13	2	14-Jan-23	Papaya	75			Note:	Update the Week No. to see the sale performance of any week.
14	2	15-Jan-23	Dragon fruits	15				Once changing the week number, then the categories and total sales will automaticallt change. And only the top 5 categories will appear on the table.
15	3	18-Jan-23	Cherry	60
16	3	18-Jan-23	Apple	75
17	3	18-Jan-23	Strawberry	70
18	3	18-Jan-23	Grapes	30
19	3	18-Jan-23	Durian	20
20	3	18-Jan-23	Orange	55
21	3	18-Jan-23	Banana	35
22	3	18-Jan-23	Pineaple	55
23	3	19-Jan-23	Orange	10
24	3	19-Jan-23	Banana	45
Summary

Cell Formulas
Range	Formula
G7:I11	G7	=LET(u,UNIQUE(FILTER(C6:C200,A6:A200=H5)),TAKE(HSTACK(SEQUENCE(MIN(5,ROWS(u))),SORT(HSTACK(u,SUMIFS(D:D,A:A,H5,C:C,u)),2,-1)),5))
A6:A24	A6	=ISOWEEKNUM(B6)
Dynamic array formulas.

 

[Sochen]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2							Change the week number from Week 2 to Week 3 as the result will automatically change in the table below
3
4
5	Week No.	Date	Categories	Sales			Week No.	2			Week No.	3
6	2	11-Jan-23	Apple	50			Rank.	Categories	Total Sales		Rank.	Categories	Total Sales
7	2	11-Jan-23	Mango	25			1	Apple	90		1	Banana	80
8	2	11-Jan-23	Banana	15			2	Papaya	75		2	Apple	75
9	2	11-Jan-23	Orange	10			3	Mango	55		3	Strawberry	70
10	2	14-Jan-23	Apple	40			4	Watermelon	20		4	Orange	65
11	2	14-Jan-23	Mango	30			5	Banana	15		5	Cherry	60
12	2	14-Jan-23	Watermelon	20
13	2	14-Jan-23	Papaya	75			Note:	Update the Week No. to see the sale performance of any week.
14	2	15-Jan-23	Dragon fruits	15				Once changing the week number, then the categories and total sales will automaticallt change. And only the top 5 categories will appear on the table.
15	3	18-Jan-23	Cherry	60
16	3	18-Jan-23	Apple	75
17	3	18-Jan-23	Strawberry	70
18	3	18-Jan-23	Grapes	30
19	3	18-Jan-23	Durian	20
20	3	18-Jan-23	Orange	55
21	3	18-Jan-23	Banana	35
22	3	18-Jan-23	Pineaple	55
23	3	19-Jan-23	Orange	10
24	3	19-Jan-23	Banana	45
Summary

Cell Formulas
Range	Formula
G7:I11	G7	=LET(u,UNIQUE(FILTER(C6:C200,A6:A200=H5)),TAKE(HSTACK(SEQUENCE(MIN(5,ROWS(u))),SORT(HSTACK(u,SUMIFS(D:D,A:A,H5,C:C,u)),2,-1)),5))
A6:A24	A6	=ISOWEEKNUM(B6)
Dynamic array formulas.

Hello Sir,

The formula return "#NAME?". I believe that I my excel is not unable to use HStack. So may I ask if there is another way to do it?
Thank you!

 

[Fluff]

Oops, could have sworn your profile showed 365.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2							Change the week number from Week 2 to Week 3 as the result will automatically change in the table below
3
4
5	Week No.	Date	Categories	Sales			Week No.	2			Week No.	3
6	2	11-Jan-23	Apple	50			Rank.	Categories	Total Sales		Rank.	Categories	Total Sales
7	2	11-Jan-23	Mango	25			1	Apple	90		1	Banana	80
8	2	11-Jan-23	Banana	15			2	Papaya	75		2	Apple	75
9	2	11-Jan-23	Orange	10			3	Mango	55		3	Strawberry	70
10	2	14-Jan-23	Apple	40			4	Watermelon	20		4	Orange	65
11	2	14-Jan-23	Mango	30			5	Banana	15		5	Cherry	60
12	2	14-Jan-23	Watermelon	20
13	2	14-Jan-23	Papaya	75			Note:	Update the Week No. to see the sale performance of any week.
14	2	15-Jan-23	Dragon fruits	15				Once changing the week number, then the categories and total sales will automaticallt change. And only the top 5 categories will appear on the table.
15	3	18-Jan-23	Cherry	60
16	3	18-Jan-23	Apple	75
17	3	18-Jan-23	Strawberry	70
18	3	18-Jan-23	Grapes	30
19	3	18-Jan-23	Durian	20
20	3	18-Jan-23	Orange	55
21	3	18-Jan-23	Banana	35
22	3	18-Jan-23	Pineaple	55
23	3	19-Jan-23	Orange	10
24	3	19-Jan-23	Banana	45
Summary

Cell Formulas
Range	Formula
H7:I11	H7	=LET(u,UNIQUE(FILTER(C6:C200,A6:A200=H5)),INDEX(SORT(CHOOSE({1,2},u,SUMIFS(D:D,A:A,H5,C:C,u)),2,-1),SEQUENCE(MIN(5,ROWS(u))),{1,2}))
A6:A24	A6	=ISOWEEKNUM(B6)
Dynamic array formulas.

 

[Sochen]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2							Change the week number from Week 2 to Week 3 as the result will automatically change in the table below
3
4
5	Week No.	Date	Categories	Sales			Week No.	2			Week No.	3
6	2	11-Jan-23	Apple	50			Rank.	Categories	Total Sales		Rank.	Categories	Total Sales
7	2	11-Jan-23	Mango	25			1	Apple	90		1	Banana	80
8	2	11-Jan-23	Banana	15			2	Papaya	75		2	Apple	75
9	2	11-Jan-23	Orange	10			3	Mango	55		3	Strawberry	70
10	2	14-Jan-23	Apple	40			4	Watermelon	20		4	Orange	65
11	2	14-Jan-23	Mango	30			5	Banana	15		5	Cherry	60
12	2	14-Jan-23	Watermelon	20
13	2	14-Jan-23	Papaya	75			Note:	Update the Week No. to see the sale performance of any week.
14	2	15-Jan-23	Dragon fruits	15				Once changing the week number, then the categories and total sales will automaticallt change. And only the top 5 categories will appear on the table.
15	3	18-Jan-23	Cherry	60
16	3	18-Jan-23	Apple	75
17	3	18-Jan-23	Strawberry	70
18	3	18-Jan-23	Grapes	30
19	3	18-Jan-23	Durian	20
20	3	18-Jan-23	Orange	55
21	3	18-Jan-23	Banana	35
22	3	18-Jan-23	Pineaple	55
23	3	19-Jan-23	Orange	10
24	3	19-Jan-23	Banana	45
Summary

Cell Formulas
Range	Formula
H7:I11	H7	=LET(u,UNIQUE(FILTER(C6:C200,A6:A200=H5)),INDEX(SORT(CHOOSE({1,2},u,SUMIFS(D:D,A:A,H5,C:C,u)),2,-1),SEQUENCE(MIN(5,ROWS(u))),{1,2}))
A6:A24	A6	=ISOWEEKNUM(B6)
Dynamic array formulas.

Omg!!! Work perfectly...
Thank you so much Sir!!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Sochen]

Hello Sir,
my sincere apologies to ask you again in this post.
May I ask if I have another column E which is "Qty", so how many make those column appear in column "J" also. I couldn't figure it out.

And is there any formula that can use in Excel 2019? like create helper column? is there any suggestion Sir?

Again, pls accept my apology.

Test16.xlsx
	A	B	C	D	E	F	G	H	I	J
5	Week No.	Date	Categories	Sales	Qty		Week No.	2
6	2	11-Jan-23	Apple	$ 50.00	20		Rank.	Categories	Total Sales	Qty
7	2	11-Jan-23	Mango	$ 25.00	25		1	Papaya	$ 75.00
8	2	11-Jan-23	Banana	$ 15.00	6		2	Mango	$ 55.00
9	2	11-Jan-23	Orange	$ 10.00	10		3	Apple	$ 36.00
10	2	14-Jan-23	Apple	$ 40.00	16		4	Orange	$ 10.00
11	2	14-Jan-23	Mango	$ 30.00	30		5	Banana	$ 6.00
12	2	14-Jan-23	Watermelon	$ 20.00	5
13	2	14-Jan-23	Papaya	$ 75.00	75
14	2	15-Jan-23	Dragon fruits	$ 15.00	5
15	3	18-Jan-23	Cherry	$ 60.00	20
16	3	18-Jan-23	Apple	$ 75.00	30
17	3	18-Jan-23	Strawberry	$ 70.00	10
18	3	18-Jan-23	Grapes	$ 30.00	15
19	3	18-Jan-23	Durian	$ 20.00	4
20	3	18-Jan-23	Orange	$ 55.00	55
21	3	18-Jan-23	Banana	$ 35.00	7
22	3	18-Jan-23	Pineaple	$ 55.00	10
23	3	19-Jan-23	Orange	$ 10.00	10
24	3	19-Jan-23	Banana	$ 45.00	18
Sheet1 (2)

Cell Formulas
Range	Formula
H7:I11	H7	=LET(u,UNIQUE(FILTER(C6:C200,A6:A200=H5)),INDEX(SORT(CHOOSE({1,2},u,SUMIFS(E:E,A:A,H5,C:C,u)),2,-1),SEQUENCE(MIN(5,ROWS(u))),{1,2}))
A6:A24	A6	=ISOWEEKNUM(B6)
Dynamic array formulas.

 

[Fluff]

How about

=LET(u,UNIQUE(FILTER(C6:C200,A6:A200=H5)),INDEX(SORT(CHOOSE({1,2,3},u,SUMIFS(D:D,A:A,H5,C:C,u),SUMIFS(E:E,A:A,H5,C:C,u)),3,-1),SEQUENCE(MIN(5,ROWS(u))),{1,2,3}))

 

[Sochen]

How about

=LET(u,UNIQUE(FILTER(C6:C200,A6:A200=H5)),INDEX(SORT(CHOOSE({1,2,3},u,SUMIFS(D:D,A:A,H5,C:C,u),SUMIFS(E:E,A:A,H5,C:C,u)),3,-1),SEQUENCE(MIN(5,ROWS(u))),{1,2,3}))

Omg!!! work perfectly... And Now I also learn to add another column if needed.
Thank you! so much...