CODEWITHSUNDEEP
excelpowerpivotpowerqueryvba
Pindub_Amateur
Pindub_Amateur

Reputation: 328

Excel PowerPivot - Working with grouped rows

I'm working on a really large excel sheet which is basically a list of users along with the applications they use. Now the problem is that there are over 60000 users with atleast 10 applications each. So its a huge sheet. 2-3 times a week I need to extract the details of the applications used by certain specific users. Sometimes it's 2 users. Sometimes its 10 users. But due to the size of the spreadsheet, this activity takes me forever (1-2 hours).

Now, the sheet is structured in the following way. 

StaffId Name       DeviceNo      Location       ApplicationCount+    AppID   Application Name

12345   John Doe   DSK-982333    Mespil Road    24+    
                                                                     123     Adobe Acrobat
                                                                     234     WinZip
                                                                     345     Google Chrome

The + sign here is to indicate the grouped rows.

Is there anyway for me to use PowerPivots to extract this information?

Upvotes: 0

Views: 111

Answers (1)

Gordon K
Gordon K

Reputation: 824

One approach would be to:

  1. Create a copy of your spreadsheet in a new workbook.
  2. Add another worksheet in the workbook that contains the StaffId (in column "A") of the people you want to filter for.

  3. In the VBA editor, insert a new module and add the following code:

    Sub FilterForUsersOfInterest()
Dim BigSheet As Worksheet
Dim ListSheet As Worksheet
Dim lastCell As Range

Set BigSheet = ActiveWorkbook.Sheets("ListOfApplications") 'Change the sheet name here to match your main data tab
Set ListSheet = ActiveWorkbook.Sheets("ListOfUsers") 'Change the sheet name here to match your sheet with the users of interest this time round

'Find the last used row on the worksheet
Set lastCell = BigSheet.UsedRange.Item(BigSheet.UsedRange.Cells.Count)

'Copy the values for Staff Id down all the rows
For iRow = 2 To lastCell.Row
    If BigSheet.Range("A" & iRow) = "" Then
        BigSheet.Range("A" & iRow) = BigSheet.Range("A" & iRow - 1)
    End If
Next iRow

'Now work your way back up the rows, deleting any rows where the StaffId is not found in the current list of "users of interest"
For iRow = lastCell.Row To 2
    If Application.WorksheetFunction.CountIf(ListSheet.Range("A:A"), BigSheet.Range("A" & iRow)) = 0 Then
        BigSheet.Range("A" & iRow).EntireRow.Delete
    End If
Next iRow
End Sub

Upvotes: 1

Related Questions