0

Below is a simple representation of a huge data and its pivot table

Data

+------+------+
| Proj | Data |
+------+------+
| ABC  | S    |
| ABC  | U    |
| ABC  | S    |
| ABC  | U    |
| ABC  | U    |
| XYZ  | U    |
| XYZ  | U    |
| XYZ  | U    |
| XYZ  | U    |
| XYZ  | U    |
+------+------+

When we apply pivot table to this data,
Coulumn Label - Proj
Row Label - Data
Value - Data

We get,

+-------+---+---+-------+
| Proj  | S | U | Total |
+-------+---+---+-------+
| ABC   | 2 | 3 |     5 |
| XYZ   |   | 5 |     5 |
| Total | 2 | 8 |    10 |
+-------+---+---+-------+

How can we show below result,
using combination of value filters and label filters on column labels
and without using row label filters (it wont be feasible as original data has too many row label)

+-------+---+---+-------+
| Proj  | S | U | Total |
+-------+---+---+-------+
| ABC   | 2 | 3 |     5 |
| Total | 2 | 3 |     5 |
+-------+---+---+-------+

Thanks!

  • What is your criteria for only having ABC in your results? It seems arbitrary that you drop XYZ from the results and then state that you can't actually filter out XYZ (or anything but ABC). – JNevill Mar 6 '15 at 21:48
  • Actual goal is to filter the pivot table to get project names which have S associated to it. Here, as XYZ is only associated with U, it needs to be filtered out. However, I cant just filter according to row as the actual data has too many projects to select individually in a row filter. So, I am looking for a way to use some combination of value and label filters. – OT5 Mar 8 '15 at 0:21
  • @OT5 If you want your question moved to a different site in the network, please "flag" it for moderator attention and request migration. – a CVn Mar 20 '15 at 14:10
0

I think you can do this nice filter in Excel 2010 too (while I'm using 2013).

enter image description here

UPDATE

This code will do it:

Sub hide_pivot_items()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("PivotTable2")
Dim cell As Range

For Each cell In pt.PivotFields("Data").PivotItems("S").DataRange
    If cell.Value = vbNullString Then
        'hiding row
        pt.PivotFields("Proj").PivotItems(ThisWorkbook.ActiveSheet.Cells( _
            cell.Row, pt.PivotFields("Proj").DataRange.Column).Value).Visible = False
    End If
Next
End Sub

Just use your actual pivot table name instead of PivotTable2 in the third line.

  • Thanks ZygD. Could we display the column U along with this filter? Like the one I mentioned in the question? – OT5 Mar 16 '15 at 17:57
  • @OT5 I have updated my answer with the coded solution. Cheers! – ZygD Mar 16 '15 at 22:23
  • I really appreciate you going through the trouble of replicating the problem and writing code as a solution. However, Is it possible to get without a code but with some combination of Label and value filters? – OT5 Mar 17 '15 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.