Custom sort issue

[liampog]

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

 

[Eric W]

Sounds like you need a helper column. Try this:

Book2
	A	B	C	D	E
1	First Name	Last Name	Role	Group	Helper
2	Abe	Smith	B2	x1	Z1Abe Smith
3	Zeke	Jones	C8	x2	Z1Zeke Jones
4	Beth	Martin	A5	x3	A5Beth Martin
5	Cathy	Allenson	A2	x1	A2Cathy Allenson
6	Sam	Snead	B1	x3	Z1Sam Snead
7	Bugs	Bunny	C4	x2	Z1Bugs Bunny
8	Jack	Black	A8	x1	A8Jack Black
9	Lorena	Ochoa	A1	x2	A1Lorena Ochoa
10	Max	Power	B2	x3	Z1Max Power
11	Lisa	Simpson	C2	x4	Z1Lisa Simpson
12	Fred	Flintstone	B8	x4	Z1Fred Flintstone
Sheet13

Cell Formulas
Range	Formula
E2:E12	E2	=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
	A	B	C	D	E
1	First Name	Last Name	Role	Group	Helper
2	Lorena	Ochoa	A1	x2	A1Lorena Ochoa
3	Cathy	Allenson	A2	x1	A2Cathy Allenson
4	Beth	Martin	A5	x3	A5Beth Martin
5	Jack	Black	A8	x1	A8Jack Black
6	Abe	Smith	B2	x1	Z1Abe Smith
7	Bugs	Bunny	C4	x2	Z1Bugs Bunny
8	Fred	Flintstone	B8	x4	Z1Fred Flintstone
9	Lisa	Simpson	C2	x4	Z1Lisa Simpson
10	Max	Power	B2	x3	Z1Max Power
11	Sam	Snead	B1	x3	Z1Sam Snead
12	Zeke	Jones	C8	x2	Z1Zeke Jones
Sheet13

Cell Formulas
Range	Formula
E2:E12	E2	=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]

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]

In that case, the formula would look like:

Book2
	A	B	C	D	E
1	First Name	Last Name	Role	Group	Helper
2	Cathy	Allenson	A2	1	001A2Cathy Allenson
3	Jack	Black	A8	1	001A8Jack Black
4	Abe	Smith	B2	1	001Z1Abe Smith
5	Lorena	Ochoa	A1	2	002A1Lorena Ochoa
6	Bugs	Bunny	C4	2	002Z1Bugs Bunny
7	Zeke	Jones	C8	2	002Z1Zeke Jones
8	Beth	Martin	A5	3	003A5Beth Martin
9	Max	Power	B2	3	003Z1Max Power
10	Sam	Snead	B1	3	003Z1Sam Snead
11	Fred	Flintstone	B8	4	004Z1Fred Flintstone
12	Lisa	Simpson	C2	4	004Z1Lisa Simpson
Sheet13

Cell Formulas
Range	Formula
E2:E12	E2	=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]

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]

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).

VBA for smarties: Contents

www.snb-vba.eu

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]

I've decided to go with the helper column much easie rI think and works, thanks!