Selecting a subset from an array

I've spent a few hours looking for a solution, but since Numbers does not support array functions, there isn't a great one. It requires you to sort each week manually, but that only takes about two seconds.

Here is my solution:

Create a second table and copy each date from your original table into the header row of the new table. In A2, add the formula

=IF(ISBLANK(Play dates::J2),Play dates::$I2)

(I've called the first table "Play dates" so change this to it's actual name)

Autofill the entire table with this formula, then each week Sort Ascending in the column you need by right-clicking on the column letter or by opening the menu from the little down arrow.

It should look like this:

Let me know if this is what you were looking for!

Is there a reason you have this Excel-like area of cells in a bigger grid of cells?

In Numbers you would typically put this in a table of its own, with the names in column A and dates across the top in a Header Row.

If you do that, you will find it is a simple matter to:

1) filter on a column (to hide the rows with 1s) in turn

2) copy-paste the names from column A into a column in a destination table.

3) Undo that filter, go on to the next column, and repeat.

This is not fully automated but would probably take at most two or three minutes.

SG

Filter data in Numbers on Mac - Apple Support

I can recommend a solution that I learned right in this community.

It is a bit laborious at first, but it has the advantage that, once set, it fills the columns automatically.

Follow my screenshot that I reduced for convenience

Add two columns after each date and as a header to the second column you add copy the date so that the date of B1 is = to D1

In C2 put this formula and then drag it throughout the column

=IF(B2=0;MAX(C$1:C1)+1;”")

With this formula you transform the names of the participants into a numerical list, leaving out those who do not play.

In D2 put this other formula with which you match the names to the numerical list obtaining the final list already ordered

=IF(ROW()−1>MAX(C);””;INDEX($A;MATCH(ROW()−1;C;0)))

Basically, this formula also acts as a filter.

This too should be dragged throughout the column.

You do the same thing in the two columns you add to each column with the date, where you just need to copy and paste the formula.

At the end hide all the columns with the numbers and get your calendar, as you see from this second Screenshot.

Sorry for the mistake, but I'm glad you solved it and that the advice was useful to you.