Previous 1 2 16 Replies Latest reply: Oct 3, 2013 4:26 PM by Jerrold Green1
Level 1 (20 points)

I need to create a formula that generate a list of names based on specific criteria. I will do my best to describe what I have already done, and what is going wrong:

1. I have created a "data sheet" that lists (among other things) student's names and which classroom teacher they go to:

2. I have created another sheet to list all the students that are associated with Teacher 1, using an IF function to automatically place the students' names on the list:

3. This works fine, until a student moves from one classroom to another. In the first screenshot above, if I change Student 8 (row 9) to work with Teacher 2, their name appears in row 9 on the second sheet:

How can I make changes on the data sheet, have the Teacher sheet automatically update, yet keep the names from populating into the same row they originated from? I have tried using absolute values in the function, but that does not seem to make a difference. Is an IF function appropriate in this case? Is there something better/more useful that I should be using?

Numbers, OS X Mountain Lion (10.8.3)
• Level 7 (29,960 points)

Jo-Alex,

You can use one of the Lookup/Match functions to grab student names and consolidate names for each teacher in separate tables. It will by dynamic.

First, I have to comment on your screen shots (good ones). It appears that you haven't yet discovered Header Rows. Column Titles and things like Teacher Name for the breakout table should be in one or more Header Rows. It makes things easier in the programming department.

Next you will be adding a column to your Data Sheet table so that you can index the students by their teacher and their order in the list. This combination of factors makes it possible to do the breakouts.

Here's an approach using the Match function to find what row the student's name is in and using the Index function to go and get that name:

The formula in the index column is:

=B&" - "&COUNTIF(OFFSET(\$B\$1, 0,0, ROW()), B)

And, the formula in the list of students under the teacher's name is:

=INDEX(DataTbl, MATCH(\$A\$1&" - "&ROW()-1, DataTbl :: C, 0), 1)

The red triangles indicate that there are more rows than students. A small addition to the expression for finding the student name can fix this:

=IF(COUNTIF(DataTbl :: B,\$A\$1)>ROW()-2,INDEX(DataTbl, MATCH(\$A\$1&" - "&ROW()-1, DataTbl :: C, 0), 1), ".")

I didn't give you this at first because the expression is already tough enough to read.

Now you will have:

Jerry

• Level 6 (11,615 points)

Two ideas in addition to Jerry's:

1) You could set up a filter on the Teacher sheet so that it shows only the rows that have a student name. This would be, by far, the simplest solution.  Use the Reorganize panel to set up a filter.

2) Jerry's method requires the addition of a new column in the data table.  There are other methods that don't require messign with the data table; it would all be done in the Teacher table. The formulas in your Teacher Sheet would be as follows:

Table 1

A1 is the teacher name.

Table 2

A2 =IFERROR(OFFSET(Data sheet::Table 1 :: A\$1,B2,0),"")

B1 has a zero (or is blank)

B2 =IFERROR(MATCH(Table 2 :: \$A\$1,OFFSET(Data sheet::Table 1 :: B\$1,B1+1,0,ROWS(Data sheet::Table 1)-B1-1,1),0)+B1,"")

Fill down from A2 & B2 to finish the table.

The formula in column B is the one doing the heavy lifting.  The formula in B2 looks in the Data Table for a match to the teacher name and returns the row number of the first match. Moving down to cell B3, the formula does the same except it is not looking at the entire Data Table, it is looking only at rows below that first match. And so on.

• Level 6 (9,042 points)

Hi jo-alex,

I see that Jerry has given an excellent solution for a breakout table and I recommend that you use that.

While I was playing around with IF and VLOOKUP (without success) I thought of the KISS principle (Keep It Simple Sir). Using the KISS principle, one table will do. This is just another way. This is how I keep a database of names, addresses and so on. Changing one cell requires only a resort of the table.

Your original database:

Student 1 is reassigned from Teacher 1 to Teacher 2:

Continued in next post...

Regards,

Ian.

• Level 6 (9,042 points)

Sorry, I had trouble posting screenshots...

Menu > Table > Show Reorganize Panel.

To get this:

Simple, but Jerry's solution has the advantage of producing a "report" for each class.

Regards,

Ian.

• Level 7 (29,960 points)

Jo-Alex,

Ian's comments regarding sorting reminded me that I forgot to mention that the main data table remains sortable without breaking any of the expressions. The roster by class tables will always display in the same order as the main table, but of course will skip names not belonging to that class.

Regarding Badunit's approach, I prefer to keep the auxiliary column (which may be hidden to preserve the format of the sheet) in the main table because it preserves the option to easily expand the class table such that teacher names go across the top and each column represents a class. The only change in the class table expression to do that would be to remove the dollar sign that pins the column reference of the teacher name, something that wasn't necessary in the first place, and to use a dollar sign to pin the references to the teacher column and index column of the data table. The revised expression for the class table would be:

=IF(COUNTIF(DataTbl :: \$B,A\$1)>ROW()-2,INDEX(DataTbl, MATCH(A\$1&" - "&ROW()-1, DataTbl :: \$C, 0), 1), ".")

That expression can be filled to all cells in the class table, even if expanded to list more than one class.

Jerry

• Level 1 (20 points)

Thanks for all the replys everyone. They have been very helpful. Using your formula, Jerrold Green1, I successfully created the aux. column. When I use the longer formula to list the students under the proper teacher, it does something odd: near the end of the formula:

...&ROW()-1, DataTbl :: \$C, 0), 1), "."

it replaces the 0 with a gray circle asking me to "find value". If I leave it be, it doesn't give me a syntax error but instead returns a period in the cell. Sounds like it needs a more info?

• Level 7 (29,960 points)

Jo,

What you are seeing is the way Numbers represents a function's options in the formula editor. There is no way for me to post in that format unless I post a graphic. Here's what it looks like in my document:

Note also how cell references are displayed as little capsules rather than plain text.

Jerry

• Level 1 (20 points)

Jerrold-

That is nearly exactly what I see. Is there something that needs to go into that gray, FIND VALUE capsule?

• Level 7 (29,960 points)

No, what you see is exactly what you want to have in this use of the function. You want to find the exact value, not just a close value, so the option chosen is "Find Value". If the exact value is not found, an error is generated, and the IFERROR function converts the error to the last parameter of the IFERROR function syntax, in this case a period character. I like using the period rather than a blank (null string) so when I see it I know it's a calculated result and not just a cell with no content.

Jerry

• Level 1 (20 points)

Thanks a lot for your help, everyone. It is working very nicely.

• Level 1 (0 points)

I like how you posted the graphic of the functions option in the formula editor and was wondering if you could post the graphic of the other two formulas. I'm trying this on an iPhone. Not sure if it will work at all.

=B&" - "&COUNTIF(OFFSET(\$B\$1, 0,0, ROW()), B)

=INDEX(DataTbl, MATCH(\$A\$1&" - "&ROW()-1, DataTbl :: C, 0), 1)

• Level 7 (29,960 points)

I'm not sure how this will help you, but here goes...

and

Jerry

• Level 1 (0 points)

When entering the formula into the iPhone I keep getting a incorrect syntax error. Look at your illustration may help me to enter it correctly.  As I said previously, not sure it this works in numbers for iPhone at all but, thank you.

• Level 7 (29,960 points)

That could be an extra comma, misplaced parenthesis, etc. Must be exactly correct syntax.

Jerry

Previous 1 2