Generating a list of names

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

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.

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.

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.

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

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

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

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)

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

and

Jerry

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.

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

Jerry

Just to let you know, I'm new at this and really not quite comprehendering it all but, my quest is why am I getting

a False value in column A2? This is the only cell which I've applied the additional expression to. See the attached. Any help would be greatly appreciated.