SORT and SORT.BY new functions - Dynamic selection of multiple columns ?

C

cydmm

New Member
Joined
Oct 17, 2015
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hi guys,

I am working on a model that let user filter data from a database though the new dynamic array Filter function with some criterias (in the enclosed example the criteria is only 1, City).
Then the model should let the user apply a "multilevel" sorting selecting from a series of cells with data validation, depending on the depth of levels the user need the sorting to be made. This selection is made through cells O2:O7 and Q2:Q7 for the corresponding sorting order.

The problem is that, using the dynamic array Sort function (but it would be exactly the same using the Sort-by function) I am only able to apply 1 single criteria sorting. I am not succeeding to build a formula that applies the sorting based upon the choices made by the user when sorting Levels are more than 1.
As per what I learnt about these two "new" functions, sorting multiple columns should be decided before writing the formula and cannot be based on flexible and dynamic criterias. Am I mistaking?

I would really appreciate your help about that, thanks a lot.

Hope to hear back from you guys,

Bye (and have an Happy Easter!)

Mark

TEST_.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1Sort by:Order:columnorder
2QuantityAscendingLevel 151
3Level 2 
4Level 3 
5Level 4 
6Level 5 
7Level 6 Boston1RegionAscending1
8Filter by CityNew York2CityDescending-1
9Los Angeles3Category
10RegionCityCategoryProductQuantityTurnoverRegionCityCategoryProductQuantityTurnoverSan Diego4Product
11EastBostonBarsBanana79179,33WestLos Angeles*******sWhole Wheat42146,585Quantity
12EastBostonBarsBran467873,29WestSan Diego*******sWhole Wheat48167,526Turnover
13EastBostonBarsCarrot12812267,37EastBostonBarsBanana79179,33
14EastBostonCookiesArrowroot8801918,4WestSan DiegoSnacksPotato Chips100168,00
15EastBostonCookiesChocolate Chip6421200,54EastNew York*******sWhole Wheat141492,09
16EastBostonCookiesOatmeal Raisin11843362,56WestLos AngelesCookiesArrowroot185403,30
17EastBoston*******sWhole Wheat7262533,74EastBostonSnacksPretzels186585,90
18EastBostonSnacksPotato Chips205344,4WestSan DiegoBarsBran196366,52
19EastBostonSnacksPretzels186585,9WestSan DiegoCookiesOatmeal Raisin202573,68
20EastNew YorkBarsBran5631052,81EastBostonSnacksPotato Chips205344,40
21EastNew YorkBarsCarrot11201982,4EastNew YorkSnacksPotato Chips326529,53
22EastNew YorkCookiesArrowroot9292025,22WestLos AngelesBarsBran349652,63
23EastNew YorkCookiesChocolate Chip470878,9WestLos AngelesSnacksPotato Chips363609,84
24EastNew YorkCookiesOatmeal Raisin4571297,88WestSan DiegoCookiesArrowroot451983,18
25EastNew York*******sWhole Wheat141492,09EastNew YorkCookiesOatmeal Raisin4571.297,88
26EastNew YorkSnacksPotato Chips326529,53EastBostonBarsBran467873,29
27WestLos AngelesBarsBran349652,63EastNew YorkCookiesChocolate Chip470878,90
28WestLos AngelesBarsCarrot12622233,74WestSan DiegoCookiesChocolate Chip496927,52
29WestLos AngelesCookiesArrowroot185403,3WestSan DiegoBarsCarrot524927,48
30WestLos AngelesCookiesChocolate Chip8371565,19EastNew YorkBarsBran5631.052,81
31WestLos AngelesCookiesOatmeal Raisin7312076,04EastBostonCookiesChocolate Chip6421.200,54
32WestLos Angeles*******sWhole Wheat42146,58EastBoston*******sWhole Wheat7262.533,74
33WestLos AngelesSnacksPotato Chips363609,84WestLos AngelesCookiesOatmeal Raisin7312.076,04
34WestSan DiegoBarsBran196366,52WestLos AngelesCookiesChocolate Chip8371.565,19
35WestSan DiegoBarsCarrot524927,48EastBostonCookiesArrowroot8801.918,40
36WestSan DiegoCookiesArrowroot451983,18EastNew YorkCookiesArrowroot9292.025,22
37WestSan DiegoCookiesChocolate Chip496927,52EastNew YorkBarsCarrot1.1201.982,40
38WestSan DiegoCookiesOatmeal Raisin202573,68EastBostonCookiesOatmeal Raisin1.1843.362,56
39WestSan Diego*******sWhole Wheat48167,52WestLos AngelesBarsCarrot1.2622.233,74
40WestSan DiegoSnacksPotato Chips100168EastBostonBarsCarrot1.2812.267,37
41
42
43
44
45
46
47
48
DATABASE
Cell Formulas
RangeFormula
U2U2=IF(Q2<>"",XLOOKUP(Q2,$AB$7:$AB$8,$AC$7:$AC$8),"")
T2:T7T2=IF(O2<>"",XLOOKUP(O2,$Z$7:$Z$12,$Y$7:$Y$12),"")
H11:M40H11=SORT(FILTER(Sales_Data,Sales_Data[City]=I8,Sales_Data),T2,U2)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
I8List=$W$7:$W$11
O2:O7List=$Z$7:$Z$13
Q2:Q7List=$AB$7:$AB$8
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
You can do that like
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRSTU
1Sort by:Order:columnorder
2CityAscendingLevel 121
3CategoryDescendingLevel 23-1
4Level 3  
5Level 4  
6Level 5  
7Level 6  
8Filter by City
9
10RegionCityCategoryProductQuantityTurnoverRegionCityCategoryProductQuantityTurnover
11EastBostonBarsBanana79179.33EastBostonSnacksPotato Chips205344.42
12EastBostonBarsBran467873.29EastBostonSnacksPretzels186585.93
13EastBostonBarsCarrot12812267.37EastBostonCookiesArrowroot8801918.4
14EastBostonCookiesArrowroot8801918.4EastBostonCookiesChocolate Chip6421200.54
15EastBostonCookiesChocolate Chip6421200.54EastBostonCookiesOatmeal Raisin11843362.56
16EastBostonCookiesOatmeal Raisin11843362.56EastBostonBiscuitsWhole Wheat7262533.74
17EastBostonBiscuitsWhole Wheat7262533.74EastBostonBarsBanana79179.33
18EastBostonSnacksPotato Chips205344.4EastBostonBarsBran467873.29
19EastBostonSnacksPretzels186585.9EastBostonBarsCarrot12812267.37
20EastNew YorkBarsBran5631052.81WestLos AngelesSnacksPotato Chips363609.84
21EastNew YorkBarsCarrot11201982.4WestLos AngelesCookiesArrowroot185403.3
22EastNew YorkCookiesArrowroot9292025.22WestLos AngelesCookiesChocolate Chip8371565.19
23EastNew YorkCookiesChocolate Chip470878.9WestLos AngelesCookiesOatmeal Raisin7312076.04
24EastNew YorkCookiesOatmeal Raisin4571297.88WestLos AngelesBiscuitsWhole Wheat42146.58
25EastNew YorkBiscuitsWhole Wheat141492.09WestLos AngelesBarsBran349652.63
26EastNew YorkSnacksPotato Chips326529.53WestLos AngelesBarsCarrot12622233.74
27WestLos AngelesBarsBran349652.63EastNew YorkSnacksPotato Chips326529.53
28WestLos AngelesBarsCarrot12622233.74EastNew YorkCookiesArrowroot9292025.22
29WestLos AngelesCookiesArrowroot185403.3EastNew YorkCookiesChocolate Chip470878.9
30WestLos AngelesCookiesChocolate Chip8371565.19EastNew YorkCookiesOatmeal Raisin4571297.88
31WestLos AngelesCookiesOatmeal Raisin7312076.04EastNew YorkBiscuitsWhole Wheat141492.09
32WestLos AngelesBiscuitsWhole Wheat42146.58EastNew YorkBarsBran5631052.81
33WestLos AngelesSnacksPotato Chips363609.84EastNew YorkBarsCarrot11201982.4
34WestSan DiegoBarsBran196366.52WestSan DiegoSnacksPotato Chips100168
35WestSan DiegoBarsCarrot524927.48WestSan DiegoCookiesArrowroot451983.18
36WestSan DiegoCookiesArrowroot451983.18WestSan DiegoCookiesChocolate Chip496927.52
37WestSan DiegoCookiesChocolate Chip496927.52WestSan DiegoCookiesOatmeal Raisin202573.68
38WestSan DiegoCookiesOatmeal Raisin202573.68WestSan DiegoBiscuitsWhole Wheat48167.52
39WestSan DiegoBiscuitsWhole Wheat48167.52WestSan DiegoBarsBran196366.52
40WestSan DiegoSnacksPotato Chips100168WestSan DiegoBarsCarrot524927.48
41
Main
Cell Formulas
RangeFormula
T2:T7T2=IF(O2<>"",XLOOKUP(O2,$Z$7:$Z$12,$Y$7:$Y$12),"")
U2:U7U2=IF(Q2<>"",XLOOKUP(Q2,$AB$7:$AB$8,$AC$7:$AC$8),"")
H11:M40H11=SORT(FILTER(Sales_Data,Sales_Data[City]=I8,Sales_Data),FILTER(T2:T7,O2:O7<>""),FILTER(U2:U7,O2:O7<>""))
O11:O12O11=FILTER(T2:T7,T2:T7<>"")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
O2:O7List=$Z$7:$Z$12
Q2:Q7List=$AB$7:$AB$8
 
Upvote 0
Solution
Fluff said:
You can do that like
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRSTU
1Sort by:Order:columnorder
2CityAscendingLevel 121
3CategoryDescendingLevel 23-1
4Level 3  
5Level 4  
6Level 5  
7Level 6  
8Filter by City
9
10RegionCityCategoryProductQuantityTurnoverRegionCityCategoryProductQuantityTurnover
11EastBostonBarsBanana79179.33EastBostonSnacksPotato Chips205344.42
12EastBostonBarsBran467873.29EastBostonSnacksPretzels186585.93
13EastBostonBarsCarrot12812267.37EastBostonCookiesArrowroot8801918.4
14EastBostonCookiesArrowroot8801918.4EastBostonCookiesChocolate Chip6421200.54
15EastBostonCookiesChocolate Chip6421200.54EastBostonCookiesOatmeal Raisin11843362.56
16EastBostonCookiesOatmeal Raisin11843362.56EastBostonBiscuitsWhole Wheat7262533.74
17EastBostonBiscuitsWhole Wheat7262533.74EastBostonBarsBanana79179.33
18EastBostonSnacksPotato Chips205344.4EastBostonBarsBran467873.29
19EastBostonSnacksPretzels186585.9EastBostonBarsCarrot12812267.37
20EastNew YorkBarsBran5631052.81WestLos AngelesSnacksPotato Chips363609.84
21EastNew YorkBarsCarrot11201982.4WestLos AngelesCookiesArrowroot185403.3
22EastNew YorkCookiesArrowroot9292025.22WestLos AngelesCookiesChocolate Chip8371565.19
23EastNew YorkCookiesChocolate Chip470878.9WestLos AngelesCookiesOatmeal Raisin7312076.04
24EastNew YorkCookiesOatmeal Raisin4571297.88WestLos AngelesBiscuitsWhole Wheat42146.58
25EastNew YorkBiscuitsWhole Wheat141492.09WestLos AngelesBarsBran349652.63
26EastNew YorkSnacksPotato Chips326529.53WestLos AngelesBarsCarrot12622233.74
27WestLos AngelesBarsBran349652.63EastNew YorkSnacksPotato Chips326529.53
28WestLos AngelesBarsCarrot12622233.74EastNew YorkCookiesArrowroot9292025.22
29WestLos AngelesCookiesArrowroot185403.3EastNew YorkCookiesChocolate Chip470878.9
30WestLos AngelesCookiesChocolate Chip8371565.19EastNew YorkCookiesOatmeal Raisin4571297.88
31WestLos AngelesCookiesOatmeal Raisin7312076.04EastNew YorkBiscuitsWhole Wheat141492.09
32WestLos AngelesBiscuitsWhole Wheat42146.58EastNew YorkBarsBran5631052.81
33WestLos AngelesSnacksPotato Chips363609.84EastNew YorkBarsCarrot11201982.4
34WestSan DiegoBarsBran196366.52WestSan DiegoSnacksPotato Chips100168
35WestSan DiegoBarsCarrot524927.48WestSan DiegoCookiesArrowroot451983.18
36WestSan DiegoCookiesArrowroot451983.18WestSan DiegoCookiesChocolate Chip496927.52
37WestSan DiegoCookiesChocolate Chip496927.52WestSan DiegoCookiesOatmeal Raisin202573.68
38WestSan DiegoCookiesOatmeal Raisin202573.68WestSan DiegoBiscuitsWhole Wheat48167.52
39WestSan DiegoBiscuitsWhole Wheat48167.52WestSan DiegoBarsBran196366.52
40WestSan DiegoSnacksPotato Chips100168WestSan DiegoBarsCarrot524927.48
41
Main
Cell Formulas
RangeFormula
T2:T7T2=IF(O2<>"",XLOOKUP(O2,$Z$7:$Z$12,$Y$7:$Y$12),"")
U2:U7U2=IF(Q2<>"",XLOOKUP(Q2,$AB$7:$AB$8,$AC$7:$AC$8),"")
H11:M40H11=SORT(FILTER(Sales_Data,Sales_Data[City]=I8,Sales_Data),FILTER(T2:T7,O2:O7<>""),FILTER(U2:U7,O2:O7<>""))
O11:O12O11=FILTER(T2:T7,T2:T7<>"")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
O2:O7List=$Z$7:$Z$12
Q2:Q7List=$AB$7:$AB$8
Click to expand...

Thank you so much Fluff.

Your solution works perfectly. It is very instructive for me. Thank you again.

Mark
 
Upvote 0
One other thing, if you change the formula in U2:U7 to
Excel Formula: 
=IF(Q2<>"",XLOOKUP(Q2,$AB$7:$AB$8,$AC$7:$AC$8),1)
it will default to ascending, rather than giving #Value until you select the order.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
XLOOKUP help (maybe), not sure what function to use
Replies
4
Views
277
MeaclH
M
M
  • Solved
Selective Match/Vlookup (?) on Various Criteria in Single Row
Replies
2
Views
276
m3ko
M
D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
189
Denny57
D
E
  • Solved
DOUBLE FILTER function to return ONLY records with most recent date
Replies
2
Views
900
elcalvo
E
P
  • Solved
Select Case in VBA
Replies
3
Views
755
rollis13
rollis13

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top