Skip navigation

Generating a list of names

763 Views 16 Replies Latest reply: Oct 3, 2013 4:26 PM by Jerrold Green1 RSS
1 2 Previous Next
jo-alexander Level 1 Level 1 (20 points)
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:

 

datatablescreenie1.png

 

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:

 

teachertablescsreenie2.png

 

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:

 

teachertablescreenie3.png

 

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)
  • Jerrold Green1 Level 7 Level 7 (28,195 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:

     

    Screen Shot 2013-05-13 at 10.14.17 am.png

    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:

     

    Screen Shot 2013-05-13 at 10.22.48 am.png

    Jerry

  • Badunit Level 6 Level 6 (10,765 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.

  • Yellowbox Level 4 Level 4 (3,910 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.

     

    Your original database:

     

    Screen Shot 2013-05-14 at 12.57.49 AM.png

    Student 1 is reassigned from Teacher 1 to Teacher 2:

     

    Screen Shot 2013-05-14 at 12.58.10 AM.png

     

    Continued in next post...

     

    Regards,

    Ian.

  • Yellowbox Level 4 Level 4 (3,910 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.

     

    Screen Shot 2013-05-14 at 1.00.38 AM.png

     

    To get this:

     

    Screen Shot 2013-05-14 at 12.59.31 AM.png

     

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

     

    Regards,

    Ian.

  • Jerrold Green1 Level 7 Level 7 (28,195 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

  • Jerrold Green1 Level 7 Level 7 (28,195 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:

     

    Screen Shot 2013-05-13 at 5.47.10 pm.png

     

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

     

    Jerry

  • Jerrold Green1 Level 7 Level 7 (28,195 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

  • keyboardman3244 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)

  • Jerrold Green1 Level 7 Level 7 (28,195 points)
    Currently Being Moderated
    Sep 30, 2013 5:27 PM (in response to keyboardman3244)

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

     

    Screen Shot 2013-09-30 at 8.24.12 pm.png

    and

    Screen Shot 2013-09-30 at 8.25.45 pm.png

    Jerry

  • keyboardman3244 Level 1 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.

  • Jerrold Green1 Level 7 Level 7 (28,195 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

1 2 Previous Next

Actions

More Like This

  • Retrieving data ...

Bookmarked By (1)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.