Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Tricky question with grouping and sorting

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 751
Default Tricky question with grouping and sorting

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

ExcelNovice wrote:
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.


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default Tricky question with grouping and sorting

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" wrote:

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

ExcelNovice wrote:
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.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 751
Default Tricky question with grouping and sorting

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: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


ExcelNovice wrote:
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" wrote:

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

ExcelNovice wrote:
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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
grouping and sorting data mb7q Excel Discussion (Misc queries) 0 March 30th 06 08:29 PM
Sorting while grouping 3 rows together... steve Excel Discussion (Misc queries) 5 June 23rd 05 10:39 PM
Microsoft Excel - Grouping and Sorting Cells Michael Blogg Excel Worksheet Functions 1 May 26th 05 09:27 PM
Microsoft Excel - Grouping and Sorting Cells Michael Blogg Excel Discussion (Misc queries) 1 May 26th 05 12:50 PM
sorting gwl Excel Discussion (Misc queries) 0 January 27th 05 07:27 PM


All times are GMT +1. The time now is 01:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"