Tricky question with grouping and sorting

[Guest]

Hey guys. I have a tricky question that I was hoping you could assist me with.

I have a spreadsheet full of data with each row representing an individual
event. Each row also belongs to a larger event. Think of it as if each row
represents a pitch in a baseball game and the groups of rows are each at-bat.
That is the best parallel I can think of.

I am trying to sort these "at-bats" by what happens on a certain "pitch"
without breaking up the "at-bats."

I'll try to put it another way... I have 100 rows of data, broken up into
groups of 5. The data that is in the first row of every group of 5 is the
most important data and I want to sort by that. Is there a way to rearrange
the groups of 5 rows without reordering the individual rows.

And yet another way... I have the following rows on a spreadsheet:
1-A-X
1-B-X
1-C-Y
1-D-Z
2-A-Y
2-B-Z
2-C-Z
2-D-X
3-A-X
3-B-Y
3-C-X
3-D-Y
4-A-Y
4-B-Z
4-C-X
4-D-Y

The number is the section, ABCD is the group, XYZ is the subgroup. I need to
reorder those rows based on what is in the XYZ subgroup but the section
always has to stay together and the group order can not change.

I want to reorder it based on what is in the A group. So it should end up
looking like:

1-A-X
1-B-X
1-C-Y
1-D-Z
3-A-X
3-B-Y
3-C-X
3-D-Y
2-A-Y
2-B-Z
2-C-Z
2-D-X
4-A-Y
4-B-Z
4-C-X
4-D-Y

The sections stayed together and the group order didnt change, it just
reordered the sections based on what was in the subgroup.

I hope one of these explanations was clear enough to understand. Any help
would be greatly appreciated. Thank you.

 

[vezerid]

So, let me see if I understand you...

The third field (X, Y, Z) will determine which group should appear
first.
Those groups which have X in the *first entry* of the group will appear
first.
The groups will keep their internal ordering.
Thus, the second field essentially does not count much for our
ordering.

If I am correct in these, some questions:
What is the criterion of ordering for the XYZ? Alphabetic?
What is the criterion of ordering the 123? Numeric?

Regards,
Kostis Vezerides

 

[Guest]

XYZ determines which groups should appear first, yes.
Groups will keep there internal ordering.
The second field does not matter for overall ordering, it only will
determine the ordering within the group.

As for your two questions, yes to both.

Thanks

 

[vezerid]

OK, I thought I would have to go to very elaborate formulas and
auxiliary columns etc, or VBA, but it seems the solution is much
simpler.

If your data occupy cells A2:C21, in D2 enter the following formula:

=IF(MOD(ROWS($C$2:C2),5)=1,CODE(C2)*10000+(COUNTIF($C$2:$C$21,C2)-COUNTIF(C2:$C$21,C2))*500,D1+1)

Sort ascending by column D.

This code assumes that the important column (C:C) has one letter, which
I suspect is not the case. In this case, the subexpression

CODE(C2)

can be replaced with the subexpression

SUMPRODUCT(CODE(MID(C2,4-ROW($1:$4)+1,1))-65,26^ROW($1:$4))

This will take into account the first 4 characters of the important
items and produce a unique
"numerization" of them. Play with the number 4: if you have shorter
codes (e.g. up to 2 characters) reduce it to 2. Do not increase it to
the maximum number of characters in the case you have long codes there.

Thus, the full formula you can use in D2 is:

=IF(MOD(ROWS($C$2:C2),5)=1,SUMPRODUCT(CODE(MID(C2,4-ROW($1:$4)+1,1))-65,26^ROW($1:$4))*10000+(COUNTIF($C$2:$C$21,C2)-COUNTIF(C2:$C$21,C2))*500,D1+1)

HTH
Kostis Vezerides