Ask Experts Questions for FREE Help !
Ask
    kamal1964's Avatar
    kamal1964 Posts: 5, Reputation: 1
    New Member
     
    #1

    Feb 12, 2010, 04:35 AM
    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?
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    Feb 12, 2010, 07:28 AM

    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.
    kamal1964's Avatar
    kamal1964 Posts: 5, Reputation: 1
    New Member
     
    #3

    Feb 12, 2010, 08:25 AM

    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.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #4

    Feb 12, 2010, 11:26 AM

    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.
    kamal1964's Avatar
    kamal1964 Posts: 5, Reputation: 1
    New Member
     
    #5

    Feb 15, 2010, 01:23 AM


    Hi JB - thanks

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

    But thanks for your help
    Attached Files
  1. File Type: xls Fill Rates test2.xls (174.0 KB, 217 views)
  2. JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #6

    Feb 15, 2010, 10:56 AM

    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.
    kamal1964's Avatar
    kamal1964 Posts: 5, Reputation: 1
    New Member
     
    #7

    Feb 15, 2010, 02:55 PM

    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
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #8

    Feb 15, 2010, 03:01 PM

    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.
    kamal1964's Avatar
    kamal1964 Posts: 5, Reputation: 1
    New Member
     
    #9

    Feb 15, 2010, 03:03 PM

    Cheers

    You have been great help, much appreciated.
    Regards
    Kam

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Collect data into 2 combo boxes from data in an access table [ 1 Answers ]

I have two combo boxes. When the form loads the db connection opens a recordset that selects all the data from the retaillocations table. When you choose the region (0-4), that number loads into a text box and the Contacts load into the forst combo box. Now I want to select only the Name based on...

Sumif function or macro [ 5 Answers ]

Hi, Need yor help. I have two sheets 1. Tracker 2. Data I have to update my GL Balance(CAD) "H Col0umn" in Tracker Sheet, for that I will take my total sum from Sum Transaction Amt from "F Coloumn" sheet2 "DATA".

Sort a Column & sum the range with same value during the sort [ 2 Answers ]

Hi, I'm trying to sort a data table by Grades & then sum the totals of the cell with same Grade. The table consists of this headers: Date, DD#, Grade, Bags so what I'm trying to do is to come up with a formula that can sort by Grade & then add the total Bags against that particular Grade and...

SUMIF with 2 criteria [ 2 Answers ]

Hello All, I'm relatively new to using functions in excel and I'm sure my problem is really very simple but I am having a hard time figuring it out. I want to use the SUMIF function to total column C but only if column A=4012 and if column B=UK. A B C G/L...

How can I sort data on a Hidden column in Excel? [ 2 Answers ]

I have a spreadsheet that has a pivot table in it. Each record has a time group column and a time nbr column in the pivot table. (i.e. Timegroup = 90 days before and a timenbr of 1 is assigned to this group, Timegroup = 60 days before and a timenbr of 2 is assigned to this group,...


View more questions Search