squishy2010
New Member
- Joined
- Oct 22, 2018
- Messages
- 4
In Excel, there is a nifty sort feature you can use to sort by several columns of information. For example, if your data looks like this:
[TABLE="width: 140"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]tree[/TD]
[/TR]
[TR]
[TD]ginger[/TD]
[TD]root[/TD]
[/TR]
[TR]
[TD]pear[/TD]
[TD]tree[/TD]
[/TR]
[TR]
[TD]grape[/TD]
[TD]vine[/TD]
[/TR]
[TR]
[TD]apricot[/TD]
[TD]tree[/TD]
[/TR]
[TR]
[TD]carrot[/TD]
[TD]root[/TD]
[/TR]
[TR]
[TD]banana[/TD]
[TD]tree[/TD]
[/TR]
[TR]
[TD]muscadine[/TD]
[TD]vine[/TD]
[/TR]
[TR]
[TD]booberry[/TD]
[TD]vine[/TD]
[/TR]
[TR]
[TD]onion[/TD]
[TD]root[/TD]
[/TR]
</tbody>[/TABLE]
You can do a sort by Type, and add a level to include Name. It then will look like this:
[TABLE="width: 137"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]carrot[/TD]
[TD]root[/TD]
[/TR]
[TR]
[TD]ginger[/TD]
[TD]root[/TD]
[/TR]
[TR]
[TD]onion[/TD]
[TD]root[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]tree[/TD]
[/TR]
[TR]
[TD]apricot[/TD]
[TD]tree[/TD]
[/TR]
[TR]
[TD]banana[/TD]
[TD]tree[/TD]
[/TR]
[TR]
[TD]pear[/TD]
[TD]tree[/TD]
[/TR]
[TR]
[TD]booberry[/TD]
[TD]vine[/TD]
[/TR]
[TR]
[TD]grape[/TD]
[TD]vine[/TD]
[/TR]
[TR]
[TD]muscadine[/TD]
[TD]vine[/TD]
[/TR]
</tbody>[/TABLE]
As you can see, it groups the list into three groups, and sorts each group independently. Roots are sorted by name first, then Trees, then Vines.
Let's say you want to do this with an array formula. The first thing you try is some SMALL(IF array formulas. Then you have to work in some MIN(SMALL(IF(COUNTIF formulas. Then you get completely nuts and go with MMULT array constructs, but to no avail. You try posting on forums, and all you get are clever responses like:
1. WHY DON'T YOU JUST CLICK THE SORT BUTTON?
A: Because I don't want to click the sort button. I want an array formula that sorts the list dynamically. That means it updates with no user interaction.
2. WHY DON'T YOU USE PIVOT TABLES?
A: Because I don't want to use pivot tables. Rather than go into the 1000 reasons why, hows about we try to find a solution with an array formula? I don't like the caching, the refreshing, etc. that comes with pivot tables.
3. WHY DON'T YOU USE HELPER COLUMNS?
A: sigh. YES, I know I could use helper columns. I DON'T WANT to use helper columns. I want to use a nice, tidy array formula. Why is this so hard to do?
4. WHY DON'T YOU BUY THIS EXCEL EXTENSION?
5. WHY DON'T YOU WRITE A CUSTOM BUBBLE SORT FUNCTION IN VBA?
Look, I know I can do that stuff. I have done that stuff. What I want to do is use an array formula to do a multi-level sort in Excel. Exhaustive searches have failed to find a solution. Would anyone on this magnificent forum have a clue how to do this in an array formula?
Many thanks- I have a feeling I'm not alone in searching for a solution to this problem. This could be the only forum on the internet that provides a solution to this if we can come up with an answer.
[TABLE="width: 140"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]tree[/TD]
[/TR]
[TR]
[TD]ginger[/TD]
[TD]root[/TD]
[/TR]
[TR]
[TD]pear[/TD]
[TD]tree[/TD]
[/TR]
[TR]
[TD]grape[/TD]
[TD]vine[/TD]
[/TR]
[TR]
[TD]apricot[/TD]
[TD]tree[/TD]
[/TR]
[TR]
[TD]carrot[/TD]
[TD]root[/TD]
[/TR]
[TR]
[TD]banana[/TD]
[TD]tree[/TD]
[/TR]
[TR]
[TD]muscadine[/TD]
[TD]vine[/TD]
[/TR]
[TR]
[TD]booberry[/TD]
[TD]vine[/TD]
[/TR]
[TR]
[TD]onion[/TD]
[TD]root[/TD]
[/TR]
</tbody>[/TABLE]
You can do a sort by Type, and add a level to include Name. It then will look like this:
[TABLE="width: 137"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]carrot[/TD]
[TD]root[/TD]
[/TR]
[TR]
[TD]ginger[/TD]
[TD]root[/TD]
[/TR]
[TR]
[TD]onion[/TD]
[TD]root[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]tree[/TD]
[/TR]
[TR]
[TD]apricot[/TD]
[TD]tree[/TD]
[/TR]
[TR]
[TD]banana[/TD]
[TD]tree[/TD]
[/TR]
[TR]
[TD]pear[/TD]
[TD]tree[/TD]
[/TR]
[TR]
[TD]booberry[/TD]
[TD]vine[/TD]
[/TR]
[TR]
[TD]grape[/TD]
[TD]vine[/TD]
[/TR]
[TR]
[TD]muscadine[/TD]
[TD]vine[/TD]
[/TR]
</tbody>[/TABLE]
As you can see, it groups the list into three groups, and sorts each group independently. Roots are sorted by name first, then Trees, then Vines.
Let's say you want to do this with an array formula. The first thing you try is some SMALL(IF array formulas. Then you have to work in some MIN(SMALL(IF(COUNTIF formulas. Then you get completely nuts and go with MMULT array constructs, but to no avail. You try posting on forums, and all you get are clever responses like:
1. WHY DON'T YOU JUST CLICK THE SORT BUTTON?
A: Because I don't want to click the sort button. I want an array formula that sorts the list dynamically. That means it updates with no user interaction.
2. WHY DON'T YOU USE PIVOT TABLES?
A: Because I don't want to use pivot tables. Rather than go into the 1000 reasons why, hows about we try to find a solution with an array formula? I don't like the caching, the refreshing, etc. that comes with pivot tables.
3. WHY DON'T YOU USE HELPER COLUMNS?
A: sigh. YES, I know I could use helper columns. I DON'T WANT to use helper columns. I want to use a nice, tidy array formula. Why is this so hard to do?
4. WHY DON'T YOU BUY THIS EXCEL EXTENSION?
5. WHY DON'T YOU WRITE A CUSTOM BUBBLE SORT FUNCTION IN VBA?
Look, I know I can do that stuff. I have done that stuff. What I want to do is use an array formula to do a multi-level sort in Excel. Exhaustive searches have failed to find a solution. Would anyone on this magnificent forum have a clue how to do this in an array formula?
Many thanks- I have a feeling I'm not alone in searching for a solution to this problem. This could be the only forum on the internet that provides a solution to this if we can come up with an answer.