Can I filter out pivot table results that only have one row for a value in column A?

Question

I created a pivot table in googlesheets, and it returns results that look like:

    first      | second | CountOf3
    --------------------------
    thing      | value  |  23
               | newVal |   3
               | cool   |  34
    that       | value  | 234
    otherThing | cool   |   4
               | newVal | 345

And I want to filter out results with just one resulting row for the item in the first column.

So in this example, that would be the row: that | value | 234.

I would like the filter to remove that row, and leave the remaining rows. This is a pivot table in a 2nd sheet that updates when Sheet1 changes.

I have been trying all day, and have not been able to come up with a solution. I was hoping there would be some sort of filter, or spreadsheet formula to do this. I've tried multiple combinations of filters, but nothing seems to work - I'm starting to wonder if this is even possible.

kirkg13 kirkg13 · Accepted Answer · 2020-11-14T05:54:50

It isn't pretty, but a brute force way is to have a check column beside your pivot table, with this formula on the first data row, ie beside "thing | value | 23".

It flags each row where the subsequent cell in column D is not blank. Then use a query (or filter) to list only the output rows you want. Note that you would hide the columns or rows with the actual (unfiltered) pivot output.

This is the simplest version, to see the logic:

=AND(LEN(D3),LEN(D4))

which results in a TRUE value for pivot chart rows that only have one value.

A more elegant version is an arrayformula, adds the header lable, and uses "Skip" as the flag for which rows to filter out.

={"Better Check";ARRAYFORMULA(IF(LEN(D3:D998)*LEN(D4:D999)*LEN(E3:E998),"Skip",))}

Note that this formula allows for a pivot table result effectively to the bottom of the sheet, but it does have a finite range, due to the constraint of checking two rows at once. It could be enhanced by using a COUNTA on the third data column to measure the exact length of the pivot table results and control the range dynamically, Like this:

={"Better Check";
ARRAYFORMULA( IF( LEN(INDIRECT("D3:D" & (COUNTA(F$3:F)+ROW(F$2)))) * 
                  LEN(INDIRECT("D4:D" & (COUNTA(F$3:F)+1+ROW(F$2)))),
                "Skip",))}

Let us know if this helps at all.

Can I filter out pivot table results that only have one row for a value in column A?

1 Answers