# Sort from Z-A with condition

#### el c

##### New Member
Hi,
I would like to sort the "Location" from Z-A for each order . The label "item" goes with the Location, they are pairs. Here is an example of what I want to do:

Book7
ABCDEFG
1OrdersitemLocationOrdersitemLocation
21367-66-400112761367-66-40011278
31311-12-141111191367-66-40011276
41311-12-14111033 to 1311-12-14111235
51311-12-141112351311-12-14111119
61367-66-400112781311-12-14111033
72418-15-001112372418-15-00111239
82418-15-001112392418-15-00111237
92418-15-001112312418-15-00111231
Sheet1
Cell Formulas
RangeFormula
E2:E9E2=A2

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Fluff

##### MrExcel MVP, Moderator
+Fluff.xlsm
ABCDEFG
1OrdersitemLocationOrdersitemLocation
21367-66-400112761367-66-40011278
31311-12-141111191367-66-40011276
41311-12-141110331311-12-14111235
51311-12-141112351311-12-14111119
61367-66-400112781311-12-14111033
72418-15-001112372418-15-00111239
82418-15-001112392418-15-00111237
92418-15-001112312418-15-00111231
Master
Cell Formulas
RangeFormula
E2:G9E2=SORTBY(A2:C9,A2:A9,1,C2:C9,-1)
Dynamic array formulas.

#### el c

##### New Member
Thank you for responding. When I copy-paste it it appears like this :

Book7
ABCDEFG
1OrdersitemLocationOrdersitemLocation
21367-66-40011276#NAME?
31311-12-14111119
41311-12-14111033
51311-12-14111235
61367-66-40011278
72418-15-00111237
82418-15-00111239
92418-15-00111231
Sheet1
Cell Formulas
RangeFormula
E2E2=SORTBY(A2:C9,A2:A9,1,C2:C9,-1)

#### sandy666

##### Well-known Member
SORTBY()
Note: This function is currently available to Office 365 subscribers in the Monthly channel. It will be available to Office 365 subscribers in the Semi-Annual channel starting in July 2020.

#### Peter_SSs

##### MrExcel MVP, Moderator
Until you get the SORTBY function, see if these, copied down, do what you want.

20 04 05.xlsm
ABCDEFG
1OrdersitemLocationOrdersitemLocation
21367-66-400112761367-66-40011278
31311-12-141111191367-66-40011276
41311-12-141110331311-12-14111235
51311-12-141112351311-12-14111119
61367-66-400112781311-12-14111033
72418-15-001112372418-15-00111239
82418-15-001112392418-15-00111237
92418-15-001112312418-15-00111231
Sort
Cell Formulas
RangeFormula
E2:E9E2=A2
F2:F9F2=INDEX(B\$2:B\$9,AGGREGATE(15,6,(ROW(B\$2:B\$9)-ROW(B\$2)+1)/((A\$2:A\$9=E2)*(C\$2:C\$9=G2)),1))
G2:G9G2=AGGREGATE(14,6,C\$2:C\$9/(A\$2:A\$9=E2),COUNTIF(E\$2:E2,E2))

#### el c

##### New Member
Thank you very much, Peter!

#### Peter_SSs

##### MrExcel MVP, Moderator
Thank you very much, Peter!
You're welcome. Thanks for the follow-up.