Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   How to sort data that contains sumif (https://www.askmehelpdesk.com/showthread.php?t=446193)

  • Feb 12, 2010, 04:35 AM
    kamal1964
    How to sort data that contains sumif
    I am trying to sort data in Excel 2003. Problem is the data range I am sorting contains sumif formulae and you cannot sort data properly

    Any help?
  • Feb 12, 2010, 07:28 AM
    JBeaucaire

    You are correct, you cannot sort data based on cells with formulas in them and get very consistent results. But you can try.

    Click GO ADVANCED and use the paperclip icon to post up your workbook. Perhaps make a second copy of the sheet in question and demonstrate manually the sort you'd like to achieve. Maybe we can figure it out.
  • Feb 12, 2010, 08:25 AM
    kamal1964

    Thx JB
    The data is sensitive so cannot post workbook here.
    I appreciate your help.
    I just want to be able to sort the column that has the sumif formulae in descending order (resulting data is in % i.e 100% , 95% etc)
    I appreciate I can to a paste-special of the data and then sort but when I send the file out I want the links to be retained to source data.
  • Feb 12, 2010, 11:26 AM
    JBeaucaire

    Creating a non-sensitive version of a workbook takes about 2 minutes... replace names the NAME1, NAME2 then copy down, Phone1,Phone2 copy down... etc.

    Very easy to create. I really can't suggest anything helpful until I see the data layout and formulas you're trying to manage.

    In a sample workbook, you COULD do a pastespecial>values and sort on a copy of the original sheet so I could see a DESIRED RESULTS page along with your Original.
  • Feb 15, 2010, 01:23 AM
    kamal1964
    1 Attachment(s)


    Hi JB - thanks

    Due to the rules at my work I unfortunately cannot attach any files.

    But thanks for your help
  • Feb 15, 2010, 10:56 AM
    JBeaucaire

    For this to work simply, you'll keep the existing Fill Rates Summary sheet as is. It does all the heavy lifting is and worth using as a prestage.

    Create a permanent second sheet with a name like "Sorted Summary" or whatever.

    1) Right-click on the new sheet tab
    2) Select VIEW CODE
    3) Paste in this activation macro:
    Code:

    Option Explicit

    Private Sub Worksheet_Activate()
    Dim LR As Long
    Application.ScreenUpdating = False
    Range("A1:O" & Rows.Count).Clear

    LR = Sheets("Fill Rates Summary").Range("A" & Rows.Count).End(xlUp).Row + 2
    Sheets("Fill Rates Summary").Range("A1:O" & LR).Copy
    Range("A1").PasteSpecial xlPasteValues
    Range("A1").PasteSpecial xlPasteFormats

    Range("B35:O" & LR).Sort Key1:=[O34], Order1:=xlDescending, Key2:=[B34], Order1:=xlAscending, _
        Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

    Application.ScreenUpdating = True
    Beep
    End Sub

    4) Close the editor
    5) Save your workbook as a macro-enabled workbook.

    Now, any time you bring this new worksheet up onscreen, you will hear a "beep" indicating the sheet has been updated.
  • Feb 15, 2010, 02:55 PM
    kamal1964

    Thx JB
    Fab Macro routine, however I was anticipating whether it was possible to sort sumif cells.
    I assume this is not the case?
    However thanks for the Macro which is an excellent workaround
  • Feb 15, 2010, 03:01 PM
    JBeaucaire

    Sorry, I thought we covered that in post #2. No, you can't sort cells with things like SUMIF() and get reliable results. Best to leave your reliable layout alone and create flat "reports" from it.
  • Feb 15, 2010, 03:03 PM
    kamal1964

    Cheers

    You have been great help, much appreciated.
    Regards
    Kam

  • All times are GMT -7. The time now is 03:40 AM.