Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Sort Data

Posted on 2012-03-14
9
Medium Priority
?
288 Views
Last Modified: 2012-03-25
I need Experts advice. Can we sort the data at Data sheet (attached the script) at the same time maintaining the row group (+)?  

Sub SortingAtoR()
Intersect(Range("A1").CurrentRegion, Range("A:R")).Sort _
    Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2"), Order2:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers
End Sub

Open in new window

SortData.xls
0
Comment
Question by:Billa7
  • 4
  • 3
9 Comments
 
LVL 4

Expert Comment

by:SmittyPro
ID: 37725476
You can record a macro removing the grouping, sort, then reapply the grouping.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37727553
It really depends on how you are sorting and what your grouping logic is.

It appears to me from your last grouping workbook I worked on with you, that doing a date then title sort which appears to be what you're doing, you would invalidate the groups.

I tested with test data....

It appears that the sort you have doesn't even sort within groups when I change the data to test, but when I remove the groups, the sort works, correctly, but then the grouping is gone.

So, if you provide the grouping logic, we can remove the group, run the sort, then add the group back.

What is the grouping logic for your rows?

Dave
0
 

Author Comment

by:Billa7
ID: 37727567
Hi Dave,

I groping the date base on week (Monday to Sunday); I do realized by data without day reference. Are we still able to group the date without day reference? if yes, that will be great.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 42

Expert Comment

by:dlmille
ID: 37727591
We can group on column A.  Is your definition of week from Sun to Saturday?
0
 

Author Comment

by:Billa7
ID: 37727762
Hi Dave,

Yes, we can do that way.
0
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 37727773
We can use the WEEKNUM function.  App creates helper column in column S on weeknum, then creates subtotals based on weeknum, then goes in and removes the subtotals giving the grouping back.

Sub SortingAtoR()
Dim lastRow As Long
Dim r As Range
Dim rDelete As Range

    On Error Resume Next
    Rows.RemoveSubtotal
    Rows.Ungroup
    On Error GoTo 0
    
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Intersect(Range("A1").CurrentRegion, Range("A:R")).Sort _
        Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2"), Order2:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers
        
    Range("S1").Value = "WeekNum"
    Range("S2:S" & lastRow).Formula = "=WEEKNUM($A2)"
    
    Range("A1:S" & lastRow).Subtotal GroupBy:=19, Function:=xlSum, TotalList:=Array(19), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        
    'remove subTotals
    For Each r In Range("A:A").SpecialCells(xlCellTypeBlanks)
        If rDelete Is Nothing Then
            Set rDelete = r.Resize(, Range("S1").Column)
        Else
            Set rDelete = Union(r.Resize(, Range("S1").Column), rDelete)
        End If
    Next r
    
    rDelete.Delete shift:=xlUp
    Range("S:S").ClearContents
End Sub

Open in new window


See attached demonstration workbook.

Dave
SortData-r1.xls
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37759471
Billa - are we done with this, ready for close out?

Dave
0
 

Author Closing Comment

by:Billa7
ID: 37762512
Thanks Dave.

Sorry for the delay.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question