Automatically sort specific data

Assume I have this list:

Sample.png

Now, here's what I want to do (preferably with formulas and functions only - no macros). In column E, I would like to automatically sort the category name based on the amounts spent in each category. In other words it should be a simple list:

Rent
Car Payment
Gas (Car)
Power Bill
Gas Bill
Car Insurance

This should automatically updated any time I update the amounts in column C. How do I do this? I'm at a complete loss...

Originally Posted by Rheanna

Hm, it looks like it does what I want, but I have no idea what the formula means. Would you mind explaining the formula at all?

Formula:

Please Login or Register  to view this content.

I'll need to start in the middle of the middle parenthesis like Excel does.

This part:
COUNTIF($B$2:$B$7,"<="&$B$2:$B$7) counts the number of values in column B that are less than or equal to each value. If you select
just the "<="&$B$2:$B$7 part of the formula in the formula bar and press the F9 key you should see this: {"<=1200";"<=451";"<=152";"<=122.4";"<=125";"<=105.5"};

then select all of this COUNTIF part and press F9 again and you should see this:

{6;5;4;2;3;1}. That is the count of all values in the list that are less than or equal to the corresponding values. So how many values are less than or equal to 1200? Well, all of them including 1200. Those counts continue; they then sorted small to large with the ROWS($1:1) function assigning 1st, 2nd, 3rd ....etc smallest by assigning 1 rows, 2 rows, 3 rows....6 rows to each number in the {6;5;4;2;3;1} starting with the 1; they just happen to correspond with the positions of the 1st, 2nd.... smallest dollar amounts.

Sorted these are then compared by the MATCH function back to another copy of COUNTIF($B$2:$B$7,"<="&$B$2:$B$7) and assigns index numbers that are then fed to the INDEX function that holds all your data...both columns.

The formula used above is array entered into cell F2 by committing it with Ctrl + Shift + Enter; then use the fill handle to drag that cell down and then across to column G.

The COLUMNS($A:A) part operates like the ROWS function assigning column index numbers to the INDEX function that tells it to fill in from the first column in your data through the second.

Change the dollar amounts in column B and the formula adjusts accordingly....sorted by amounts with corresponding items following.

It's more complicated to tell than it is to actually do. Just do the select/highlight of each individual step, in sequence, in your formula....starting from the inside working your way out as you go pressing the F9 key. In this way the formula kind of explains itself. It's tedious at first, but worth the effort if you want to understand how a formula is working...passing its values on to each successive function until it presents the solution.

Whew......hope that helped.

Edit: I just caught a problem with this solution. It is only half a solution. If one of your amounts is duplicated it will simply repeat the item in column A. There is a remedy....working on it.

You're welcome. Glad it worked.

P.S. Be sure to mark this thread as [SOLVED].

BTW: lest I forget; the formula in column G is not my creation; I got the idea from micope21 in this thread Post #5:

http://www.excelforum.com/excel-form...ng-column.html

Sorry for the oversight.