Generating a list of names

JoAlex,
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

Like (0)


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)B11,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.

Like (0)


Yellowbox New South Wales, Australia
Hi joalex,
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.

Like (0)


Yellowbox New South Wales, Australia

Like (0)


JoAlex,
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

Like (0)


joalexander Outer Rim Territories
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?

Like (0)


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

Like (1)


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

Like (0)


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

Like (0)


joalexander Outer Rim Territories
Thanks a lot for your help, everyone. It is working very nicely.

Like (0)


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)

Like (0)



Like (0)


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.

Like (0)


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

Like (0)

Actions
More Like This
 Retrieving data ...
Legend
 This solved my question  10 points
 This helped me  5 points