Need a formula . . .

Question

Level 1

8 points

Need a formula . . .

I am looking for a way to take a master list of names as shown in "Table 1" and automatically sort those marked entries into new tables which correspond to their categorization as shown in the "Red Group," "Blue Group," etc.

Of course, the check-boxes return a TRUE or FALSE according to whether they are checked.I am imagining something which functions similarly to the COUNTIF or SUMIF commands in that it would cycle through the list and grab only the names which correspond to the search criteria, e.g., "B2:B9=TRUE", and populate the new list accordingly.

Probably rudimentary for someone who does this for a living, but it is beyond my present knowledge. Thanks in advance for any help you can give.

BTW, Am using Numbers 3.5.2; OS 10.10.2, if it matters.

Thanks again,

Scott

iMac, OS X Mavericks (10.9.4)

Posted on Mar 25, 2015 1:53 PM

Reply

Answer 1

Mar 25, 2015 3:19 PM in response to DirtyDawg

I suggest switch to using pop-up menus in a single column. To make the pop-up menu,

- type in the list of items in the menu

- select the cells with the item:

- no open the "Cell Formatter" and select "Pop-Up Menu"

Nothing will appear to have happened... but click on one of those cells to see that it is a popup menu with the item that was in the cell originally selected.

now select the cell that has "None" selected, copy

select cells in column C (except for the header), paste to refill the cells with the none selection

Enter the names in columns A and B and select the group

use the contextual menu in column C to sort the table by group

you can also filter by group

you will have to use the clue of missing rows that the table is filtered.

Use the Filter Formatter to control filtering and turn it off:

Reply

Answer 2

Hiroto

Level 5

7,461 points

Mar 25, 2015 4:00 PM in response to DirtyDawg

Hello

I'd calculate the row indices by incremental matching. Something like this.

Red A1 A2 =IF(LEN($C2)>0,INDEX(Data::A,$C2,1),"") A3 =IF(LEN($C3)>0,INDEX(Data::A,$C3,1),"") A4 =IF(LEN($C4)>0,INDEX(Data::A,$C4,1),"") A5 =IF(LEN($C5)>0,INDEX(Data::A,$C5,1),"") B1 B2 =IF(LEN($C2)>0,INDEX(Data::B,$C2,1),"") B3 =IF(LEN($C3)>0,INDEX(Data::B,$C3,1),"") B4 =IF(LEN($C4)>0,INDEX(Data::B,$C4,1),"") B5 =IF(LEN($C5)>0,INDEX(Data::B,$C5,1),"") C1 index C2 =IFERROR(MATCH(TRUE,Data::C,0),0) C3 =IFERROR(MATCH(TRUE,OFFSET(Data::C,C2,0,ROWS(Data::C)-C2,1),0)+C2,"") C4 =IFERROR(MATCH(TRUE,OFFSET(Data::C,C3,0,ROWS(Data::C)-C3,1),0)+C3,"") C5 =IFERROR(MATCH(TRUE,OFFSET(Data::C,C4,0,ROWS(Data::C)-C4,1),0)+C4,"")

Notes.

Formula in C3 can be filled down.

Formula in A2 can be filled down and right across A2:B5.

For Green, Blue, Yellow tables, replace Data::C with Data::D, Data::E, Data::F respectively.

Tables are built in Numbers v2.

Good luck,

H

Reply

Answer 3

DirtyDawg Author

Level 1

8 points

Mar 30, 2015 10:17 AM in response to Hiroto

Thanks to both of you for the responses. Sorry for the delay here, had other things taking priority. I believe Hiroto's solution is going to serve my needs best, however I do have a difficulty arising from it.

In columns A and B, whenever the formula results in a FALSE response to the "IF" condition, I'm getting a "0" in that cell. This is throwing off another calculation which depends upon those cells remaining empty. Thoughts on how to go about eliminating that "0"?

Reply

Answer 4

Hiroto

Level 5

7,461 points

Mar 30, 2015 7:01 PM in response to DirtyDawg

Hello

As far as I can tell with Numbers v2, value of blank cell retrieved by formula is normally 0. You may concatenate it to "" to produce empty string instead of 0 but this way the result is always text and not number.

E.g.,

Red A1 A2 =IF(LEN($C2)>0,""&INDEX(Data::A,$C2,1),"") A3 =IF(LEN($C3)>0,""&INDEX(Data::A,$C3,1),"") A4 =IF(LEN($C4)>0,""&INDEX(Data::A,$C4,1),"") A5 =IF(LEN($C5)>0,""&INDEX(Data::A,$C5,1),"") B1 B2 =IF(LEN($C2)>0,""&INDEX(Data::B,$C2,1),"") B3 =IF(LEN($C3)>0,""&INDEX(Data::B,$C3,1),"") B4 =IF(LEN($C4)>0,""&INDEX(Data::B,$C4,1),"") B5 =IF(LEN($C5)>0,""&INDEX(Data::B,$C5,1),"") C1 index C2 =IFERROR(MATCH(TRUE,Data::C,0),0) C3 =IFERROR(MATCH(TRUE,OFFSET(Data::C,C2,0,ROWS(Data::C)-C2,1),0)+C2,"") C4 =IFERROR(MATCH(TRUE,OFFSET(Data::C,C3,0,ROWS(Data::C)-C3,1),0)+C3,"") C5 =IFERROR(MATCH(TRUE,OFFSET(Data::C,C4,0,ROWS(Data::C)-C4,1),0)+C4,"")

As for blankness test, ISBLANK() returns false for cell which contains formula. LEN()=0 test would work.

E.g.,

Blankness Test A1 isblank() A2 =ISBLANK(Red::A2) B1 len()=0 B2 =LEN(Red::A2)=0

Regards,

H

Reply

Answer 5

DirtyDawg Author

Level 1

8 points

Mar 31, 2015 9:38 AM in response to Hiroto

This is perfect. Thanks!

Reply