Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    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]
    - Ricky

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    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
    Selection.Sort Key1:=Range("F24"), Order1:=xlDescending, Header:=xlGuess _
    , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Range("F21").Select
    End Sub</pre>

    - Ricky

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting a List (XP)

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

Posting Permissions

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