Filter selection across columns

Topic

New Reply

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!

Reply | Quote

Replies

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

Reply | Quote

Fantastic! I will give a try!! Thanks so much!

Reply | Quote

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

41447-Liz1

Code:

Public Sub TransposeData()
On Error Resume Next
Application.ScreenUpdating = False
[COLOR=”#008000″]’—————————————-
‘DECLARE AND SET VARIABLES[/COLOR]
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”)
[COLOR=”#008000″]’—————————————-
‘SET LINES TO SKIP, CLEAR DATA AND FORMATTING[/COLOR]
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
[COLOR=”#008000″]’—————————————-
‘TRANSPOSE DATA TO TRANSPOSEDDATA SHEET[/COLOR]
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
[COLOR=”#008000″]’—————————————-
‘SORT SHEET BY STATUS[/COLOR]
    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
[COLOR=”#008000″]’—————————————-
‘GROUP AND SUBTOTAL[/COLOR]
    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

Reply | Quote