Sorting a List by using dropdowns

[lost_in_the_sauce]

My data set is categorized by store/metric/month, with store in column A, metric (sales, cogs, payroll, etc) in column B. C1-N1 is Jan-Dec, and everything below that is numbers pulled from a tab that contains a data export from Smartsheets. Column A & B repeat enough that there is every possible store/metric combo.

I'd like to have another tab that has a lost of every store in columns with all the metrics in the rows that will populate/reorder high to low based on 2 drop down boxes - one for month and one for metric.
Is this possible? I've been stumped trying to engineer it.

 

[SanjayGMusafir]

Is this possible?

With Excel 365 it's very much possible.

Please share a sample data using XL2BB from where you want to pull records to help you in a better way.

 

[lost_in_the_sauce]

Urban Air Financial Performance by Park 2022.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Park	Category	January	February	March	April	May	June	July	August
2	Main St	Revenue	161274	146176	413116.97	138502.82	167541	218278.43	0	176873.57
3	Main St	COGS	10436	14426	37616.68	23034.05	25259.99	52183.63	0	25586.87
4	Main St	Gross Profit	150838	131750	375500.29	115468.77	142281.01	166094.8	0	151286.7
5	Main St	Payroll	47289	42830	131959.5	37941.87	39598	63371.42	0	52668.56
6	Main St	Marketing	-5	0	1500	750	750	750	0	750
7	Main St	Insurance	5360	5365	12219.82	5365.02	12059	7452.62	0	6691
8	Main St	Occupancy	37966	38604	83548.66	37013.81	41532	44795.3	0	76501
9	Main St	Other	19897	24684	72079.95	38702.24	29953.88	28897.88	0	40558.81
10	Main St	Total Opex	110507	111483	301307.93	119772.94	123892.88	145267.22	0	177169.37
11	Main St	EBITDA	40331	20267	74192.36	-4304.17	18388.13	20827.58	0	-25882.67
12	Main St	EBITDA %	25%	14%	18%	-3%	11%	10%	-	-15%
13	Main St	% COGS	6%	10%	9%	17%	15%	24%	-	14%
14	Main St	% GP	94%	90%	91%	83%	85%	76%	-	86%
15	Main St	% Payroll	29%	29%	32%	27%	24%	29%	-	30%
16	Main St	% Marketing	0%	0%	0%	1%	0%	0%	-	0%
17	Main St	% Insurance	3%	4%	3%	4%	7%	3%	-	4%
18	Main St	% Occupancy	24%	26%	20%	27%	25%	21%	-	43%
19	Main St	% Other OpEx	12%	17%	17%	28%	18%	13%	-	23%
20	Main St	% OpEx	69%	76%	73%	86%	74%	67%	-	100%
21	Chicago St	Revenue	683433.54	1304813.94	494203.4	412691.43	275129.77	225493.72	508379.04	268894.48
22	Chicago St	COGS	69172.38	126160.83	62226.56	67780.67	41830.16	25714.29	64247.24	33632.51
23	Chicago St	Gross Profit	614261.16	1178653.11	431976.84	344910.76	233299.61	199779.43	444131.8	235261.97
24	Chicago St	Payroll	136275.88	178065.24	109204.92	73053.48	68441.44	74935.64	283533.4	68464.22
25	Chicago St	Marketing	29051.66	44931.57	31577.96	20804.93	17279.13	11304.88	20999.98	15808.2
26	Chicago St	Insurance	23730.26	2783.64	23943.88	13040.13	16560.73	17025.58	35169.16	22096.47
27	Chicago St	Occupancy	61755.98	90511.65	30383.4	30963.31	29565.93	29310.71	55840.77	30131.91
28	Chicago St	Other	65381.74	115076.94	68943.51	54456.62	54363.81	34690.73	61403.41	30743.1
29	Chicago St	Total Opex	316195.52	431369.04	264053.67	192318.47	186211.04	167267.54	456946.72	167243.9
30	Chicago St	EBITDA	298065.64	747284.07	167923.17	152592.29	47088.57	32511.89	-12814.92	68018.07
31	Chicago St	EBITDA %	44%	57%	34%	37%	17%	14%	-3%	25%
32	Chicago St	% COGS	10%	10%	13%	16%	15%	11%	13%	13%
33	Chicago St	% GP	90%	90%	87%	84%	85%	89%	87%	87%
34	Chicago St	% Payroll	20%	14%	22%	18%	25%	33%	56%	25%
35	Chicago St	% Marketing	4%	3%	6%	5%	6%	5%	4%	6%
36	Chicago St	% Insurance	3%	0%	5%	3%	6%	8%	7%	8%
37	Chicago St	% Occupancy	9%	7%	6%	8%	11%	13%	11%	11%
38	Chicago St	% Other OpEx	10%	9%	14%	13%	20%	15%	12%	11%
39	Chicago St	% OpEx	46%	33%	53%	47%	68%	74%	90%	62%
Data Sort

Cell Formulas
Range	Formula
C2:J3,C5:J9,C21:J22,C24:J28	C2	=SUMPRODUCT((Data!$B$2:$B$1214=C$1)*(Data!$J$1:$P$1=$B2)*(Data!$F$2:$F$1214=$A2)*(Data!$J$2:$P$1214))
C4:J4,C23:J23	C4	=C2-C3
C10:J10,C29:J29	C10	=C9+C8+C7+C6+C5
C11:J11,C30:J30	C11	=C4-C10
C12:J12,C31:J31	C12	=IFERROR(C11/C2,"-")
C13:J13,C32:J32	C13	=IFERROR(C3/C2,"-")
C14:J14,C33:J33	C14	=IFERROR(C4/C2,"-")
C15:J15,C34:J34	C15	=IFERROR(C5/C2,"-")
C16:J16,C35:J35	C16	=IFERROR(C6/C2,"-")
C17:J17,C36:J36	C17	=IFERROR(C7/C2,"-")
C18:J18,C37:J37	C18	=IFERROR(C8/C2,"-")
C19:J19,C38:J38	C19	=IFERROR(C9/C2,"-")
C20:J20,C39:J39	C20	=IFERROR(C10/C2,"-")

 

[lost_in_the_sauce]

For "Park," there are 157 in this list, each with the metrics listed for each month.

I'd like to have a tab with the 157 parks in column A (probably starting at row 10) and the columns going right are the metrics, that reorder the list by a specific metric based on the two drop downs above, month and metric.

 

[SanjayGMusafir]

Thanks for sharing sample data. Unfortunately I can't copy the data as it's linked to some another sheet in your database. Still would try to help you.

 

[lost_in_the_sauce]

Urban Air Financial Performance by Park 2022.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Park	Category	January	February	March	April	May	June	July	August
2	Urban Air Ahwatukee	Revenue	161274	146176	413116.97	138502.82	167541	218278.43	0	176873.57
3	Urban Air Ahwatukee	COGS	10436	14426	37616.68	23034.05	25259.99	52183.63	0	25586.87
4	Urban Air Ahwatukee	Gross Profit	150838	131750	375500.29	115468.77	142281.01	166094.8	0	151286.7
5	Urban Air Ahwatukee	Payroll	47289	42830	131959.5	37941.87	39598	63371.42	0	52668.56
6	Urban Air Ahwatukee	Marketing	-5	0	1500	750	750	750	0	750
7	Urban Air Ahwatukee	Insurance	5360	5365	12219.82	5365.02	12059	7452.62	0	6691
8	Urban Air Ahwatukee	Occupancy	37966	38604	83548.66	37013.81	41532	44795.3	0	76501
9	Urban Air Ahwatukee	Other	19897	24684	72079.95	38702.24	29953.88	28897.88	0	40558.81
10	Urban Air Ahwatukee	Total Opex	110507	111483	301307.93	119772.94	123892.88	145267.22	0	177169.37
11	Urban Air Ahwatukee	EBITDA	40331	20267	74192.36	-4304.17	18388.13	20827.58	0	-25882.67
12	Urban Air Ahwatukee	EBITDA %	25%	14%	18%	-3%	11%	10%	-	-15%
13	Urban Air Ahwatukee	% COGS	6%	10%	9%	17%	15%	24%	-	14%
14	Urban Air Ahwatukee	% GP	94%	90%	91%	83%	85%	76%	-	86%
15	Urban Air Ahwatukee	% Payroll	29%	29%	32%	27%	24%	29%	-	30%
16	Urban Air Ahwatukee	% Marketing	0%	0%	0%	1%	0%	0%	-	0%
17	Urban Air Ahwatukee	% Insurance	3%	4%	3%	4%	7%	3%	-	4%
18	Urban Air Ahwatukee	% Occupancy	24%	26%	20%	27%	25%	21%	-	43%
19	Urban Air Ahwatukee	% Other OpEx	12%	17%	17%	28%	18%	13%	-	23%
20	Urban Air Ahwatukee	% OpEx	69%	76%	73%	86%	74%	67%	-	100%
21	Urban Air Akron	Revenue	683433.54	1304813.94	494203.4	412691.43	275129.77	225493.72	508379.04	268894.48
22	Urban Air Akron	COGS	69172.38	126160.83	62226.56	67780.67	41830.16	25714.29	64247.24	33632.51
23	Urban Air Akron	Gross Profit	614261.16	1178653.11	431976.84	344910.76	233299.61	199779.43	444131.8	235261.97
24	Urban Air Akron	Payroll	136275.88	178065.24	109204.92	73053.48	68441.44	74935.64	283533.4	68464.22
25	Urban Air Akron	Marketing	29051.66	44931.57	31577.96	20804.93	17279.13	11304.88	20999.98	15808.2
26	Urban Air Akron	Insurance	23730.26	2783.64	23943.88	13040.13	16560.73	17025.58	35169.16	22096.47
27	Urban Air Akron	Occupancy	61755.98	90511.65	30383.4	30963.31	29565.93	29310.71	55840.77	30131.91
28	Urban Air Akron	Other	65381.74	115076.94	68943.51	54456.62	54363.81	34690.73	61403.41	30743.1
29	Urban Air Akron	Total Opex	316195.52	431369.04	264053.67	192318.47	186211.04	167267.54	456946.72	167243.9
30	Urban Air Akron	EBITDA	298065.64	747284.07	167923.17	152592.29	47088.57	32511.89	-12814.92	68018.07
31	Urban Air Akron	EBITDA %	44%	57%	34%	37%	17%	14%	-3%	25%
32	Urban Air Akron	% COGS	10%	10%	13%	16%	15%	11%	13%	13%
33	Urban Air Akron	% GP	90%	90%	87%	84%	85%	89%	87%	87%
34	Urban Air Akron	% Payroll	20%	14%	22%	18%	25%	33%	56%	25%
35	Urban Air Akron	% Marketing	4%	3%	6%	5%	6%	5%	4%	6%
36	Urban Air Akron	% Insurance	3%	0%	5%	3%	6%	8%	7%	8%
37	Urban Air Akron	% Occupancy	9%	7%	6%	8%	11%	13%	11%	11%
38	Urban Air Akron	% Other OpEx	10%	9%	14%	13%	20%	15%	12%	11%
39	Urban Air Akron	% OpEx	46%	33%	53%	47%	68%	74%	90%	62%
Data Sort

 

[SanjayGMusafir]

the columns going right are the metrics

The column B in your sample data you call metrics or something else. Terms can vary in different part of the world.

And thanks for sharing the new sample data

 

[lost_in_the_sauce]

Swapped category to Metrics in B1

 

[SanjayGMusafir]

I just tried working on data you shared.

Have you tried Table Slicers - I think they can do what you want without much effort.

 

[lost_in_the_sauce]

I just tried working on data you shared.

Have you tried Table Slicers - I think they can do what you want without much effort.

I haven't, I don't have tons of experience with slicers