Sort from Z-A with condition

el c

New Member
Joined
Mar 24, 2020
Messages
16
Office Version
365
Platform
Windows
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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
38,737
Office Version
365
Platform
Windows
How about
+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
Joined
Mar 24, 2020
Messages
16
Office Version
365
Platform
Windows
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
Joined
Oct 24, 2015
Messages
5,049
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
Joined
May 28, 2005
Messages
44,424
Office Version
365
Platform
Windows
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
Joined
Mar 24, 2020
Messages
16
Office Version
365
Platform
Windows
Thank you very much, Peter!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,673
Messages
5,445,905
Members
405,368
Latest member
mistersend

This Week's Hot Topics

Top