Custom sort issue

liampog

Active Member
Joined
Aug 3, 2010
Messages
305
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

Column A: "First Name"
Column B: "Last Name"
Column C: "Role"
Column D: "Group"

"Role" can be a text string of A, B or C followed by a number of 1 to 8
"Group" is a number from 0 to 7

I want to apply the following sort order:

1. Group first in ascending numerical order
2. Role in ascending alphabetical order looking at only the first letter (ignoring the number)
3. First Name in ascending alphabetical order
4. Last Name in ascending alphabetical order

I'm having an issue with the 2nd Sort Key. I want it to put all the As first in ascending Name alphabetical order and then all the Bs and Cs together in Name ascending alphabetical order. So you would ultimately end up with Bs and Cs mixed together in a random order that depends on the persons First Name

If I simply just set the 2nd Sort Key to Column C, it orders them in alphanumerical order (which you would expect): A1, A2, A3, etc, up to C8. If I remove the 2nd Sort Key (sort by role) it is almost as I want it to be, but then the As are all mixed together.

Is there a Custom Order I can set that will solve this?

I hope I've explained myself well enough and I'm hoping someone can help me out.

Thanks
Liam
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,633
Sounds like you need a helper column. Try this:


Book2
ABCDE
1First NameLast NameRoleGroupHelper
2AbeSmithB2x1Z1Abe Smith
3ZekeJonesC8x2Z1Zeke Jones
4BethMartinA5x3A5Beth Martin
5CathyAllensonA2x1A2Cathy Allenson
6SamSneadB1x3Z1Sam Snead
7BugsBunnyC4x2Z1Bugs Bunny
8JackBlackA8x1A8Jack Black
9LorenaOchoaA1x2A1Lorena Ochoa
10MaxPowerB2x3Z1Max Power
11LisaSimpsonC2x4Z1Lisa Simpson
12FredFlintstoneB8x4Z1Fred Flintstone
Sheet13
Cell Formulas
RangeFormula
E2:E12E2=IF(LEFT(C2)="A",C2,"Z1")&A2&REPT(" ",20-LEN(A2))&B2


Your data is in A:D as you described, but you need to add an E column, with the formula in E2. Then you can sort by column E. You'll get this result:

Book2
ABCDE
1First NameLast NameRoleGroupHelper
2LorenaOchoaA1x2A1Lorena Ochoa
3CathyAllensonA2x1A2Cathy Allenson
4BethMartinA5x3A5Beth Martin
5JackBlackA8x1A8Jack Black
6AbeSmithB2x1Z1Abe Smith
7BugsBunnyC4x2Z1Bugs Bunny
8FredFlintstoneB8x4Z1Fred Flintstone
9LisaSimpsonC2x4Z1Lisa Simpson
10MaxPowerB2x3Z1Max Power
11SamSneadB1x3Z1Sam Snead
12ZekeJonesC8x2Z1Zeke Jones
Sheet13
Cell Formulas
RangeFormula
E2:E12E2=IF(LEFT(C2)="A",C2,"Z1")&A2&REPT(" ",20-LEN(A2))&B2


Let me know if this isn't actually the order you want, I was a little confused. But the helper formula can be adapted to a different sort order if need be.
 

liampog

Active Member
Joined
Aug 3, 2010
Messages
305
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Eric

Yes this works, the groups are just numbers though (without the x).

The Group would be sorted first into ascending numerical order and then in each group of people it would sort as you have shown.

A helper column would work, however, it's something I'd prefer to avoid (I know I can hide the column but still). Is there nothing in terms of CustomSort that can be utilised?

Thanks
Liam
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,633
In that case, the formula would look like:

Book2
ABCDE
1First NameLast NameRoleGroupHelper
2CathyAllensonA21001A2Cathy Allenson
3JackBlackA81001A8Jack Black
4AbeSmithB21001Z1Abe Smith
5LorenaOchoaA12002A1Lorena Ochoa
6BugsBunnyC42002Z1Bugs Bunny
7ZekeJonesC82002Z1Zeke Jones
8BethMartinA53003A5Beth Martin
9MaxPowerB23003Z1Max Power
10SamSneadB13003Z1Sam Snead
11FredFlintstoneB84004Z1Fred Flintstone
12LisaSimpsonC24004Z1Lisa Simpson
Sheet13
Cell Formulas
RangeFormula
E2:E12E2=TEXT(D2,"000")&IF(LEFT(C2)="A",C2,"Z1")&A2&REPT(" ",20-LEN(A2))&B2


And no, I don't think there's a way to use a Custom sort to do this without a helper column. There are Custom Sort Lists, but I don't think you can use one to say A < (B = C). Maybe someone else has an idea. Or you could use a custom VBA macro to do it.
 

liampog

Active Member
Joined
Aug 3, 2010
Messages
305
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Eric

Thanks again for updating the helper column. I'll see if I get some more replies.

If it helps, I am actually using VBA to run this sorting (not just the Sort via the Excel menus) within the Worksheet_Change area of the sheet.

Do you have any VBA ideas?

Thanks
Liam
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,633
How are you sorting within VBA? Are you still using the Range.Sort method? If so, you could add a column, insert the helper formula, sort by that column, then delete the column. If that doesn't appeal, you can load the data into either an ArrayList (which has a .Sort method) or a SortedList (which sorts automatically), using for a key the output from the formula (which you'd create in VBA, not a worksheet formula).


Or write your own sort routine (Google "VBA quick sort", or find it on this forum). Again, you'd have to build the key that the sort routines uses.

That's all the ways I can think of to sort off hand.
 

liampog

Active Member
Joined
Aug 3, 2010
Messages
305
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I've decided to go with the helper column :) much easie rI think and works, thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,988
Messages
5,599,217
Members
414,297
Latest member
dalkarl

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