## Generating a list of names

762 Views 16 Replies Latest reply: Oct 3, 2013 4:26 PM by Jerrold Green1
Previous Next
Calculating status...
Currently Being Moderated
May 13, 2013 6:00 AM

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 (28,150 points)
Currently Being Moderated
May 13, 2013 7:22 AM (in response to jo-alexander)

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 (10,760 points)
Currently Being Moderated
May 13, 2013 8:05 AM (in response to jo-alexander)

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 4 (3,900 points)
Currently Being Moderated
May 13, 2013 8:18 AM (in response to jo-alexander)

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.

Student 1 is reassigned from Teacher 1 to Teacher 2:

Continued in next post...

Regards,

Ian.

• Level 4 (3,900 points)
Currently Being Moderated
May 13, 2013 8:23 AM (in response to Yellowbox)

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 (28,150 points)
Currently Being Moderated
May 13, 2013 9:22 AM (in response to jo-alexander)

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 7 (28,150 points)
Currently Being Moderated
May 13, 2013 2:50 PM (in response to jo-alexander)

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 7 (28,150 points)
Currently Being Moderated
May 14, 2013 11:13 AM (in response to jo-alexander)

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

• Calculating status...
Currently Being Moderated
Sep 30, 2013 5:17 PM (in response to Jerrold Green1)

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 (28,150 points)
Currently Being Moderated
Sep 30, 2013 5:27 PM (in response to keyboardman3244)

and

Jerry

• Level 1 (0 points)
Currently Being Moderated
Sep 30, 2013 6:15 PM (in response to Jerrold Green1)

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 (28,150 points)
Currently Being Moderated
Sep 30, 2013 7:07 PM (in response to keyboardman3244)

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

Jerry

Previous Next

#### More Like This

• Retrieving data ...