Results 1 to 9 of 9

Thread: Sorting data by two different criteria

  1. #1

    Sorting data by two different criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Is there a way to sort a column of data to alternate the results. I have a column that can only have MAJOR or MID as the data in it & i want to make it alternate them. I also am sorting the rows on a different column of hours till service due first.

    So i need to get the hours from lowest to highest then sort the major & mid & make it alternate them.



    Am i asking too much???????
    Last edited by Simon Lloyd; 2011-06-16 at 04:48 AM. Reason: Changed title to reflect content

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Can you give us a sample workbook that shows what you're starting with on one sheet, and how you want it sorted on another sheet? Only needs to be 1-2 dozen lines or so, but might make it a bit clearer to follow what you're after.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    i have only got 2 lots of 10 rows of data & my file size is 2.8MB & is too large to upload. How can i get it small enough to upload?

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Can you put it in a zip file?

    Sent from my LG-E900h using Board Express
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    got it in a zip. thanks
    Attached Files Attached Files

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Quote Originally Posted by spud View Post
    i have only got 2 lots of 10 rows of data & my file size is 2.8MB & is too large to upload. How can i get it small enough to upload?
    Okay, so for next time, copy your data (only) into a new workbook. (Paste it as values to the new workbook.) This one has hundreds of number formats in it which are bloating the size incredibly. If you created a new file for this, then you have something seriously wrong with Excel on your system. If you copied it out of an exisiting file, you may want to investigate some cleanup of it as well, as it looks part way corrupted to me.

    Now, on to the question at hand...

    In D1 give a header like "Order"
    In D2 enter the following formula and copy it down: =COUNTIF($C$1:C2,C2)

    Now sort your data based on that column and that should do it.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    thankyou very much, i will write the numbers next time instead of copy & paste.

    That works sweet

    Cheers

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Hi Spud,

    I've knocked up some code to remove the duplicate cell styles that exist in your workbook. Based on the version you uploaded here, you had over 26,000 duplicate styles, which are doing nothing but taking up space in your workbook.

    To use this, you need to:
    • Copy the code as I've provided below
    • Open your workbook
    • Press Alt+F11
    • Find your workbook in the project explorer (usually at left) and expand it
    • Right click your workbook and choose Insert->Module
    • Paste this code in the module you see there
    • Go back to the Excel window
    • Press Alt + F8
    • Choose to run the "RemoveDuplicateStyles" macro
    Once you do, watch in the bottom left as the system counts off the progress of duplicate styles deleted. (I suggest you do try this on a copy of your workbook first!)

    Here's the code:
    Code:
    Sub RemoveDuplicateStyles()
    'Author:    Ken Puls (www.excelguru.ca)
    'Purpose:   Remove duplicated styles from workbook
        Dim lStylesRemain As Long
        Dim lCurrentStyle As Long
        Dim lCount As Long
        With ActiveWorkbook
            'Record initial count of styles
            lStylesRemain = .Styles.Count
            For lCurrentStyle = 1 To lStylesRemain
                'If last style, exit the loop
                If lCurrentStyle = lStylesRemain Then Exit For
                
                'Check if current style exists in next style name
                If InStr(1, .Styles(lCurrentStyle + 1), .Styles(lCurrentStyle)) > 0 Then
                
                    'It exists, so delete it
                    .Styles(lCurrentStyle + 1).Delete
                    
                    'Roll back counters for loop to ensure no styles are missed
                    lCurrentStyle = lCurrentStyle - 1
                    lStylesRemain = lStylesRemain - 1
                    
                    'Cache count of styles deleted and update statusbar to show progress
                    lCount = lCount + 1
                    Application.StatusBar = "Styles Deleted: " & lCount
                End If
            Next lCurrentStyle
            'Clean up statusbar and tell the user what happened
            Application.StatusBar = False
            MsgBox "All done.  Deleted " & lCount & " duplicate styles!" & vbNewLine & _
                   .Styles.Count & " styles remain in this workbook."
        End With
    End Sub
    Once you're done, your file should save MUCH smaller and open faster. You may want to remove the code module if you don't usually use macros though. To do that, go back into the visual basic editor, find the module you inserted in the project explorer, right click it and choose Delete. (You don't need to export it). At that point you can close it and save the file.

    Hope this helps,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Quote Originally Posted by spud View Post
    thankyou very much, i will write the numbers next time instead of copy & paste.
    Copy and Paste will work, but instead of pasting as you did, right click the target area, choose "Paste Special --> Values". Then you don't get the cell formatting.

    I still think the workbook you copied it from has issues though, and you should really get those sorted. Try the macro I provided and I'm sure you're file will drop in size significantly.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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