Toggle row visibility in Numbers possible?

I added a new column, column I

I1 is a checkbox. Checked = show all rows. Unchecked = show only the first occurrence of the author's name (provided the table is sorted by name)

Formula in I2 =IF(AND(D2=OFFSET(D2,−1,0),E2=OFFSET(E2,−1,0), I$1≠TRUE),FALSE, TRUE)

Fill down to complete the column

The formula requires the table to be sorted by author.

Put a filter on column I as shown in the screenshot.

I could have used the more simple formula =IF(AND(D2=D1,E2=E1,I$1≠TRUE),FALSE, TRUE) but this one can get messed up if you drag rows around or insert or delete a row. The formula I suggest does not rely on cell references to any row but its own so it does not have those problems.

To clean it up you can make column I text color white or set the text opacity to zero so you don't see all the trues and falses. Or you could use a word such as "Filtered" instead of TRUE, a null string "" instead of FALSE, and change the filter to be "text is 'Filtered' ". That way it will be more obvious that the results are being filtered.

You might also want to consider using the Categories feature with categories for last name and first name. Then you can collapse the entire thing to just the names with a simple option-click on a disclosure triangle (I assume the iOS version has something equivalent to option-click). You won't need column I or formulas (though they are in the screenshots below). You can switch Categories on and off pretty easily to get back to the original tabular look.

How many rows is your table going to be, maximum? I've come up with a few ways to make it work like you want but Numbers gets painfully slow and pretty much unusable with too many of some functions. OFFSET is one of them. Other functions I'd like to use like COUNTIFS get very slow when the number of rows to be looked at gets large. I've been beating my head against the wall trying to come up with something that will be usable.

Jagmurcredrea wrote:

Your Categories suggestion works also, but as you suggest, but not in iOS, sadly. 🥲

Are you having trouble using Categories on the iPad? Just tap at the column letter, choose Column Actions, and Add Category for ... I did that on the column with the last name of the author, and it works well.

SG

Given that Categories works the same on iOS as on the Mac and you can expand/collapse the entire list of authors with one action or expand/collapse a single author with a single click, it sure seems to fit the bill for what you need and it will be VASTLY better than formulas and filtering.

Trick is to select column first -

SG

SGIII, is there a way on the iPad to collapse/expand all the categories in one step, like option-click does on the Mac?

Expand Peer Groups and Collapse Peer Groups are easy to access.

SG

Categories works well, I think, for your stated purpose on Mac, iPad and iPhone. It's easy to collapse and expand groups.

I too prefer Numbers for the Mac to Numbers on iPad.

SG

SGIII wrote:

Expand Peer Groups and Collapse Peer Groups are easy to access.

SG

Found it. Thanks