Sort assistance

rf78801

New Member
Joined
Jul 26, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Basic sort but I can't figure it out.

Four columns, multiple rows of data. First column is ORDER. 2nd col is HIGH. 3rd col is MED. 4th col is LOW. There is an ORDER entry (numeric) in every row followed by character string data in HIGH/MED/LOW (but only one of the three).

After the sort macro executes I would like the top rows to be any HIGH data entries sorted by ORDER, then MED sorted by ORDER, then LOW sorted by ORDER. All this independent of the alphanumeric content in columns 2-4. Right now it's sorting columns 2-4 alphabetically and jumbling column 1.

Thanks for any assistance!

R
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,391
Welcome to MrExcel.

You could do this with a helper column. Try:

Book1
ABCDE
1OrderHighMidLowHelp
26One1
33Two2
44Three3
588Five1
61Six2
721Seven3
833Eight2
97Nine3
1077Ten1
1111Eleven2
122Twelve3
13
Sheet2
Cell Formulas
RangeFormula
E2:E12E2=IF(B2<>"",1,IF(C2<>"",2,3))


Then custom sort it by Help, then Order, and you get:


Book1
ABCDE
1OrderHighMidLowHelp
26One1
377Ten1
488Five1
51Six2
63Two2
711Eleven2
833Eight2
92Twelve3
104Three3
117Nine3
1221Seven3
13
Sheet2
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,546
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Following Eric's idea, you could try this and only need to sort on the Help column (small to large)

20 07 27.xlsm
ABCDE
1OrderHighMidLowHelp
26One0.8333
33Two1.6667
44Three2.75
588Five0.9886
61Six1
721Seven2.9524
833Eight1.9697
97Nine2.8571
1077Ten0.987
1111Eleven1.9091
122Twelve2.5
Sort
Cell Formulas
RangeFormula
E2:E12E2=MATCH("?*",B2:D2,0)-1/A2


20 07 27.xlsm
ABCDE
1OrderHighMidLowHelp
26One0.8333
377Ten0.987
488Five0.9886
51Six1
63Two1.6667
711Eleven1.9091
833Eight1.9697
92Twelve2.5
104Three2.75
117Nine2.8571
1221Seven2.9524
Sort
Cell Formulas
RangeFormula
E2:E12E2=MATCH("?*",B2:D2,0)-1/A2
 

Forum statistics

Threads
1,141,130
Messages
5,704,452
Members
421,350
Latest member
jake9951

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
Top