1. Sorting a List (XP)

In cells B24:B58, I have labels (text). In the adjacent cells, C24:C58, I have sales amounts. The C cells are formulas which pull the numbers from another location on the sheet.

I want to either enter formulas in the E & F columns or attach a macro to a button that will resort the data from B & C, high to low. What would be the simplest way to accomplish this?

More Details (if needed):
<UL><LI>B24:B58 contains Monday Breakfast, Monday Lunch, Monday Afternoon, Monday Dinner, Monday Late, Tuesday Breakfast, Tuesday Lunch, etc. Total 35 rows.
<LI>C24:C58 contains the dollar amount of sales that correspond with the dayparts in the B column. These figures are pulled from a chart that the user completes elsewhere on the sheet.
<LI>I need the list re-worked in the range of E24:F58, in sorted order, high to low. This would allow me to see, for example, that Friday Lunch is the top meal period for the week. Basically, this would rank all the weekly meal periods (35).[/list]

2. Re: Sorting a List (XP)

Using the macro recorder, I copied the range, pasted the values and then sorted the new list. So, I ended up with this code attached to a button. Could this have been accomplished using formulas instead?
<pre>Sub Button2_Click_SortList()
Range("B24:C58").Select
Selection.Copy
Range("E24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E24:F58").Select
Application.CutCopyMode = False
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("F21").Select
End Sub</pre>

3. Re: Sorting a List (XP)

An alternative:
Copy B24:B58 to E24:E58.
In cell F24, enter this formula:

=VLOOKUP(E24,\$B\$24:\$C\$58,2,FALSE)

and fill down to F58, or double click the fill grip in the lower right corner of F24.

Each time you want to sort, select cell F24 and click the Sort Descending toolbar button.

4. Re: Sorting a List (XP)

Thanks Hans <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•