Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Mar 2006
    Location
    Philadelphia, PA
    Posts
    30
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Filter selection across columns

    Hi, I have a spreadsheet with multiple P&L's of clinical studies. I want to be able to sort on the totals based on the status of the various studies.
    Example..sort on all "Open" studies to receive grand totals.

    So far I'm thinking of placing each p&l on a separate spreadsheet in an Excel Workbook, then create a spreadsheet to total all of my balances.

    Decided to share for other ideas. Thanks!
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    ab,

    Howdy neighbor.

    Using the transpose function, I moved all your data to a second sheet where you will now be able to filter or sort as you mentioned above.

    HTH,
    Maud
    Attached Files Attached Files

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    ab2537 (2015-07-26)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    ab,

    The following code will automatically transpose your data from your "Lung" Sheet to the "TransposedData" sheet. It will then sort, group, and subtotal your data. You can add as many studies as you like in every other column as you have done; the code will adjust. Upon viewing the TransposedData sheet, it will update showing any additional changes you may have added.

    HTH,
    Maud

    Liz1.png

    Code:
    Public Sub TransposeData()
    On Error Resume Next
    Application.ScreenUpdating = False
    '----------------------------------------
    'DECLARE AND SET VARIABLES
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim nolines() As Variant, LstCol As Integer, col As Integer, row As Integer
    Dim I As Integer, J As Integer, K As Integer
    Set ws1 = Worksheets("Lung")
    Set ws2 = Worksheets("TransposedData")
    '----------------------------------------
    'SET LINES TO SKIP, CLEAR DATA AND FORMATTING
    nolines = Array(6, 14, 15, 16, 17, 18, 22, 23, 28, 30, 32, 33, 45)
    LastCol = ws1.Cells(5, Application.Columns.Count).End(xlToLeft).Column
    row = 6
    LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).row
    If LastRow = 5 Then LastRow = 6
    ws2.Range("A6:AC" & LastRow).ClearContents
    ws2.Range("A6:AC" & LastRow).RemoveSubtotal
    ws2.Range("A6:AC" & LastRow).ClearOutline
    On Error GoTo 0
    '----------------------------------------
    'TRANSPOSE DATA TO TRANSPOSEDDATA SHEET
    For J = 3 To LastCol Step 2
        col = 1
        For I = 5 To 46
            For K = 0 To UBound(nolines)
                If I = nolines(K) Then GoTo Skip
            Next K
            ws2.Cells(row, col) = ws1.Cells(I, J)
            col = col + 1
    Skip:
        Next I
        row = row + 1
    Next J
    '----------------------------------------
    'SORT SHEET BY STATUS
        LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).row
        ws2.Sort.SortFields.Clear
        ws2.Sort.SortFields.Add Key:=Range("B5:B" & LastRow), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ws2.Sort
            .SetRange Range("A5:AC" & LastRow)
            .Header = xlYes
            .Apply
        End With
    '----------------------------------------
    'GROUP AND SUBTOTAL
        ws2.Range("A5:AC" & LastRow).Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(9, 10, 11, _
            12, 13, 14, 15, 16, 18, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29), Replace:=True, PageBreaks _
            :=False, SummaryBelowData:=True
        ws2.Outline.ShowLevels RowLevels:=2
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    ab2537 (2015-07-26)

  6. #4
    Lounger
    Join Date
    Mar 2006
    Location
    Philadelphia, PA
    Posts
    30
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Fantastic! I will give a try!! Thanks so much!

Posting Permissions

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