Sorting with multiple fields, then by a final grouping column

[Ken Soona]

The title isn't as clear as I'd like but it's the best I can think of.

I have data that I need to sort using multiple fields, and then, put keep in grouped pairs. There is an example below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Group[/TD]
[TD]v1[/TD]
[TD]v2[/TD]
[TD]v3[/TD]
[TD]v4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to sort the data for v1 - v4 in some combination of ASC/DESC. How doesn't matter, it will change. The trick is that, after I have it sorted, I then need to sort it such that the Group variable values are always next to each other. They do not need to be in ASC or DESC order, they just need to be next to each other. I cannot figure out how to sort like this in Excel.

I need something that essentially says

Sort1 = by v1 ASC, v2 DESC, v3 ASC, v4 ASC
Sort 2 = Sort1 by Group

I apologize if this is unclear. I think what I need to do is described, but I cannot for the life of me figure out how to do this.

And, I am not familiar with VBA, or macros. If that is the answer, they need to be explained step by step, or simply stated that they are the answer, b/c that's beyond my Excel chops. I could do that in SQL, SPSS, or R, though.

Thank you in advance for any contribution or help.

KS

 

[rooirokbokkie]

The problem you have is that sorting a matrix like you have renders the sorting procedures mutually exclusive. You cannot sort v1 independently from v2 and v3 and v4. You can only sort them relative to one another. And when you add a sorting procedure at the end where the groups are together it's going to scramble your relatively sorted v1-v4 matrix anyway?

If this is not a problem then a simple non-VBA way to do it, is to use the custom sort function twice. First you'll add 4 levels (one for v1 to v4) and then select whether each one should sort smallest to largest or vice verse. Then after you completed the sorting, you can simply go through a second round of sorting, but this time via group. Just don't add group into the first round of sorting as this won't result in the groups being together, as it will essentially sort group relative to the last level that you put in (v4 for example).

 

[Ken Soona]

Yes, you are absolutely correct, thank you for your reply, and your suggestion. I will try it and see if I can get my data to end up the way I need.

Thank you very much for your input, this is approaching the top of my level of knowledge and your input is great.