Create lists from a match of criteria

Question

mrcoates14 Author

Level 1

12 points

Create lists from a match of criteria

Hello I have just moved from excel to numbers and am trying to get a spreadsheet working, which I had in excel.

From the table below you can see that I have names in the left column.

I also have row headers, in this case column B is jump 360 , C is ice skating.

If a pupil would like to go on that particular trip I put a Y in that cell that intersects their name and the activity.

In the highlighted cell what i would like is to now be able to generate a list of names of all pupils who have a Y for that trip.

In excel I used an array formula =IFERROR(INDEX($A$1:$A$166,SMALL(IF(B$1:B$166="Y",ROW(B$1:B$166)),ROW(A4))),"") This worked well as it automatically updated the list of names if a Y was put in the appropriate cell.

Can I do something similar in numbers?

If it is completely different all I want to do is to be able to keep track of which pupils want to go on each trip.

MacBook Pro, OS X El Capitan (10.11)

Posted on Apr 13, 2016 6:58 AM

Reply

Answer 1

Barry

Level 9

60,023 points

Apr 13, 2016 11:20 AM in response to mrcoates14

Hi mrcoates,

This is essentially the same situation as described in your earlier question regarding rosters for sports. The solutions offered there are not specific to the names being in "row headers," and would likely apply to this question as well.

Regards,

Barry

Reply

Answer 2

SGIII

Level 8

37,550 points

Apr 13, 2016 12:42 PM in response to mrcoates14

I can't follow Barry's link, but before I tried I had worked out something like this:

Set up "index" columns for each of the activities columns (you can hide these extra columns later). The formula here in E2, filled right and down, is:

=IF(B2<>TRUE,"",COUNTIF(B$1:B2,TRUE))

In each of the "list" tables, you could do something like this:

In Jump 360 cell A2, filled down:

=IFERROR(INDEX(Activities::A,MATCH(ROW()−1,Activities::E,0)),"")

In Ice Skating cell A2, filled down:

=IFERROR(INDEX(Activities::A,MATCH(ROW()−1,Activities::F,0)),"")

In Another cell A2, filled down:

=IFERROR(INDEX(Activities::A,MATCH(ROW()−1,Activities::G,0)),"")

SG

Reply