formula question in Numbers

More than a gut feeling, Wayne.

Here's a test of filling the tables using a random sort of the repeated list of 30 tables over a range of 120 students.

After filling in 13 columns, I ran SG's Flag Duplicates service on the first 10 students, with the results shown below:

Definitely requires a more systematic seating assignment algorithm to avoid the duplicates rather than a random assignment that requires removing the duplicates after the fact.

Regards,

Barry

Mark,

Since you posted this in a Numbers for mac OS X forum and since Apple doesn't have a Google docs forum I am posting a solution for Numbers.

You should create a new document, then title the sheet "Dining List"... like this:

Create a table, then size so it has 3 columns as shown. Then make the first row of the table a header row and name the table "Students". Keep the names of sheets and tables the same as this example so the formulas I post will work correctly.

make sure the table has 211 rows and add multiples of 7 rows when you add rows (since a table seats 7 students plus on teacher)

select cell A2, then type (or copy and paste from here) the formula:

=ROW()−1

shorthand for this is

A2=ROW()−1

To fill down:

select cell A2, copy

select cells A2 thru the end of column A, paste

Fill in the names in column C (I just made up names)

Now duplicate the table "Students" and rename the table "Faculty". Update the names as needed.

Now create a second sheet title "Seating":

the first row should be a header row

select cell D1 and type "1", then select cell E1 and type "2", now select cells D1 and E1, then hover the cursor over the left edge until you see the yellow fill control appear. click and hold on the yellow fill control and drag to the right to auto fill the sequence. expand the table as needed to create the number of weeks needed.

A2=INT((ROW()−2)÷8)+1

B2=MOD((ROW()−2), 8)+1

C2=IF(B2≠1, MAX($C$1:C1)+1, "")

select cells A2 thru C2, copy

select cells A2 thru the end of column C, paste

D2=IF($C2≠"", VLOOKUP(1+MOD(($C2−1)−7×(D$1−1), MAX(Students::$A)), Students::$A:$C, 3),VLOOKUP($A2,Faculty::$A:$C,3))

select cell D2, copy

select cells D2 thru the end of column AG (In my table this is cell AG241), paste

now you have a predictable pattern with names already filled in. You can select the column for the week, then past values (using the menu item "Edit > Paste Formula Results" into a new table on a weekly basis to make adjustments or substitutions

Hi Mark,

This might do the trick:

The key appears to be to install an extra (virtual) table.

Table 1, named Seeds, calculates a series of index numbers, that will be read by a formula on the second table to assign tables to each student (row) for each week (column). No names, or even a column for names at this point, as there will be several extra 'virtual students' destined to sit at thee same table through the full term. (Perhaps these are the faculty members.).

Here's what the top part of this table looks like:

The table contains one formula, entered in A2, then filled right to AE2 and down to row 246:

A2: (ROW()−1+QUOTIENT(ROW()−1,31))×COLUMN()+QUOTIENT(ROW()−1,31)

The pair of parentheses shown in bold will appear as 'ordinary' characters in the formula editor; the rest will be formatted according to Numbers's style.

The second table, 'Dist 1' (short for Distribution 1) reads each number and converts it to a table assignment using the numbers from 0 to 30. I've hidden a few rows to show an anomaly that occurs at regular intervals on this table, and will be handled on the next.

One formula on this table, entered in A2 and filled to AE246, after which error triangles start appearing.

A2: MOD(INDEX(Seed::$A:$AE,ROW(),COLUMN()),31)

INDEX copies the value from the same cell in Seed, MOD divides that number by 31, and returns the remainder.

The Quotient function in the formula on Seed server as a stepper to prevent this table from simply repeating the same set of seating assignments for each set of 30 (or 31) students. This results in a row of identical values on periodic rows.

Table 3 ('HC1') provides a means of fixing and editing the values generated on 'Dist 1' without destroying the formulas on 'Dist 1'.

The formula is a simple cell reference to the same cell on Dist 1:

A2: =Dist 1::A2

Fill right to AE2 and down to row 242 (the second row of 7s on Dist 10.

When the formulas finish calculating, and with all cells (except the header row) selected:

Copy.

Go Edit > Paste Formula Results.

This replaces the formula in each cell with the last result calculated there, fixing those values.

Delete each of the rows containing a string of identical values.

Table 4, 'Trimmed & filled'

Duplicate HC1, and place this formula below into A2.

A2: =IF(HC1::A2=0,HC1::$AE2,HC1::A2)

Fill right to AD2, and down to the last row of the table.

The purpose of this formula is to copy the values from HC1, plugging the holes (zeroes) by replacing them with the value in column AE of that row.

When done, delete column AE (on this table), then do the Copy > Paste Formula Results to create a fixed seating assignment chart that you can then add student names to by adding a new column to the left of column A.

For convenience, I'll copy the contents of the final table in a new post below. You can copy it from there, and investigate to see if it fits the bill.

Regards,

Barry

Hi SG,

Downloaded the Automator service and tried it on a table with 120 students, two columns filled with random table assignments, and deliberate duplicates entered in three rows of column 3.

1. The service worked well when the selection is limited to a single row—which would require 210 repetitions of selecting the next row, then running the service.
2. If the whole table is selected, the service flags the second and later occurrence of every table number with no regard to whether it applies to the same student as the first (ie whether it is in the same row). Is that easily changeable?
3. If the table number is corrected so that there is no previous occurrence of that value, the colour fill flagging that cell is not removed-even if the service is run again. Clearing the flags currently requires selecting all cells containing table assignments, opening the Cell format inspector, and setting all cells to 'no fill'.
4. Running the service on a 121 row by 14 column table with data in the first three columns, four cells of the fourth column and the header row only of the remaining 10 columns took 1m 44s (1:23 on a second run where I wasn't scrolling down to keep the action in view, but just let Numbers do it).

Point 2 is probably the one to address in making this a more useful service. Run once for each week (or less often), the speed issue is likely one that can be lived with, as is the need to manually clear the flags.

Regards,

Barry

Mark,

I am confused as to how a student can sit at a unique table. I imagine lunch tables are long table where more than one student eats. If you mean seat then I think I understand. How many seats are there? and how many students?

Can you post an example of your table?

You should know that this forum is for Numbers for macOS X so the solution we present may not apply 100% to Google docs

marknovom wrote:

Is there a formula that can search Row 2 for any duplicate numbers so that the student doesn't sit at the same table twice during the year.

Since formulas and extra rows and columns can get a little cumbersome, what I do in similar situations where I want to highlight duplicates is use this Flag Duplicates Automator Service (Dropbox download link). This contains a short AppleScript but you don't have to know anything about AppleScript to use it. Simply select the cells in the row yu want, choose Flag Duplicates from the Numbers > Services menu, and the duplicates are highlighted.

To install just double-click the downloaded .workflow package, give permission if necessary at System Preferences > Security & Privacy, and make sure Automator.app is checked at System Preferences > Security & Privacy > Privacy > Accessibility. It should take less than a minute to install, and thereafter is just menu pick away.

SG

So there are roughly 8 people per table where 1 faculty member is "fixed" with a table.

That means there are 7 open spots per table.

7*30 is 210 seat selections.

So are there 30 weeks in the term in question?

I would avoid the "random" assignment and use a pattern-- too late for this term since you are 25 weeks. A pattern will make certain that you achieve your goal.

What is the goal stated in words. Is it to have each student sit at a table with a different faculty member on a weekly basis? Is it each student sit with a different faculty member AND different students?

Know the specific goal will help understand the actual problem. My gut tells me this will never be solved by a random assignment. If you have a pattern you play back each term this the particular students names are not the issue. Eventually a student ID must be matched to a name for creating seating charts.

marknovom wrote:

I'm looking for a formula that I will have in every row to tell me if the new table number I input for the new week was already used in that row

Hi Mark,

That's what the Automator Service does. It will tell you if the number suggested by your Google solution has already been used in that row. Have you tried it?

SG

The method I posted earlier is one particular way to do this. It moves students in groups of eight to each table so they (as a group) eat with a different faculty member.

Of course there are other methods but without your guidance of the desired rule I'm just guessing

mark,

Perhaps you should post this question in a google forum dedicated to Google products rather than an Apple product. Of course you can solve the problem in Numbers at home, then copy and paste the result into a google document.

marknovom wrote:

Is there a way to use Automator in Google Docs? Unfortunately our school doesn't use Apple products. I have apple products at home.

Hi Mark,

I'm the one who posted the Automator solution to flag duplicates, so perhaps you were addressing that question to me(?). Sorry the Automator solution wasn't helpful to you. Since you posted here I think we all might have assumed you were interested in an Apple-based solution. You might find the Automator Service useful in your other work in Numbers, though, as trying to identify duplicates is a common problem and it makes that easy.

As for using Automator with Google Docs, the answer is in general is "no" (though if you're using a Mac useful things can be done via the clipboard that will work well with Google Docs). In my Automator Service is a simple AppleScript that reads the data and marks duplicates. Google Docs has its own scripting language based on JavaScript called Google Apps Script. (Apple also supports scripting based on JavaScript that I could have used instead of AppleScript in Automator).

In any case, if the school is using Google Docs (and presumably, for this problem, Google Sheets in particular) then you should find active support in their forums.

Your question reminds of my own school days. We had exactly the system you describe! Few of us gave much thought to the administrative thought and complexity behind it. We just knew we each had to endure those "bad" table assignments with faculty members we might have preferred to avoid. None of us could escape those assignments, though, which of course was the whole idea.🙂

SG

Pasted content of the last table: