Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
grouping and sorting data | Excel Discussion (Misc queries) | |||
Sorting while grouping 3 rows together... | Excel Discussion (Misc queries) | |||
Microsoft Excel - Grouping and Sorting Cells | Excel Worksheet Functions | |||
Microsoft Excel - Grouping and Sorting Cells | Excel Discussion (Misc queries) | |||
sorting | Excel Discussion (Misc queries) |