Sorting data by two different criteria

spud

New member
Joined
Jun 16, 2011
Messages
4
Reaction score
0
Points
0
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 a moderator:

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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.
 

spud

New member
Joined
Jun 16, 2011
Messages
4
Reaction score
0
Points
0
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?
 

spud

New member
Joined
Jun 16, 2011
Messages
4
Reaction score
0
Points
0
got it in a zip. thanks
 

Attachments

  • excelgur.zip
    180.8 KB · Views: 14

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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. :)
 

spud

New member
Joined
Jun 16, 2011
Messages
4
Reaction score
0
Points
0
thankyou very much, i will write the numbers next time instead of copy & paste.

That works sweet

Cheers
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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 ([URL="http://www.excelguru.ca"]www.excelguru.ca[/URL])
'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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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.
 
Top