Numbers 3.6.1
I have a list of people who are assigned schedule numbers in seniority order. Once a number is awarded to one person it is not available to subsequent people, unless a number is available more than once (e.g. numbers "10" and "15" in the reference list). Is there a way to do this in Numbers?
Example:
To return something along these lines:
MacBook Pro with Retina display, OS X El Capitan (10.11.4)
That fixed the error, and it almost works! Here's what I'm getting:
Once it gets to Bids row 8 and finds the column C value gone from the pool (taken by the bidder in row 7), it returns a zero to Assign A8. It seems to be returning the value in Bids C unless it's gone from the pool until it gets to row 13, then returns all zeros in Assign A even if the value in Bids C is still in the pool.
Interestingly, the first number in Pool row 8 (also row 11 and all such rows) disappears when the formula finds the Bids column C value missing from the pool:
Thank you, Barry, for a brilliant solution! I'm sorry, for my own sake, that it has taken me so long to get back to this problem, and I apologize to you for my delayed response (and gratitude) for your timely suggestion. I have followed your instructions, but I'm getting a "The formula contains a syntax error" message at Assign C3 [=IFERROR(IF(AND(COUNT($B3:B3)<1,B=""),VALUE(MID(Pool::$B2,FIND(Bids::C3,Pool::$ B2),2)),""),"")].
All of the rest of the tables are performing as expected.
Thank you for your help.
C
Hi cnickflight,
Checked back to see if I had saved this one, and found I had.
Here are three copies of the formula from Assign::C3.
The first is copied from C3 of the working table.
The second is copied from my post of May 1, 2016 above.
The third is copied from your post of November 6 2017.
Note that while the left ends of all three are vertically aligned, the right ends are not—the first is a bit shorter.
IFERROR(IF(AND(COUNT($B3:B3)<1,B=""),VALUE(MID(Pool::$B2,FIND(Bids::C3,Pool::$B2 ),2)),""),"") (working table)
IFERROR(IF(AND(COUNT($B3:B3)<1,B=""),VALUE(MID(Pool::$B2,FIND(Bids::C3,Pool::$ B2),2)),""),"") (my post)
IFERROR(IF(AND(COUNT($B3:B3)<1,B=""),VALUE(MID(Pool::$B2,FIND(Bids::C3,Pool::$ B2),2)),""),"") (c'flight's post)
....^
The culprit is an extra character—a single space between "$" and "B2" .........here ^
Delete that space, and the error should be gone.
Regards,
Barry
Hi c'
Had time for a brief look at this tonight. I didn't get the zeros you're getting, but I did get an interesting result on row 12, after changing the last choice in that row from 10 to 28. The assigned choice was 10, which is correct for the list given:
1, 2, 3, 4, 5, 6 ,7, 8, 9, 28
The first item in the pool to match any choice in that row would be the 1 in 10, since the character "1" does not appear before that, and the formula returns two characters, starting at the position where it finds the search value, "1".
Will try to get another look at it tomorrow evening.
Regards,
Barry
Hi cnickflight,
"I have a list of people who are assigned schedule numbers in seniority order."
Several questions arise:
As you can see, I don't have a clear idea of the question you are asking, or of how the question applies to the tables illustrated. More information is needed.
Regrds,
Barry
Hi Barry,
Thank you for patiently attempting to understand a very poorly put question.
The reference list table is incompletely represented here, but is a list of available schedules identified by number (1-95, say). Employees choose from this list when selecting their monthly schedule. Complicating matters is that some of the schedule numbers are available multiple times- see #10 and 15. I wish it weren't so, but there it is.
Column B is a list of employee ID numbers listed in seniority order. Columns C-K (and beyond- truncated here) represent each employee's expressed schedule preference, or "bid list", in order of priority. Once an employee selects her choice from the reference list, the choice is not available to subsequently listed employees (see exception above). In the example, employee 695645 selected #28, so it was not available to employee 696501, and she was awarded her next available choice- #26.
I could always assign an arbitrary identifier to these values e.g. 10a, 10b, or 101, 102, etc. (the reference number list never exceeds 100, so numbers greater than 100 could stand in for these schedule numbers that are available multiple times. However, the employees' bid lists will obviously not include the arbitrary identifier, so I would have to change these manually in the bid list- not too big a deal. If dealing with this extra level of complexity is too much, I can ignore the multiple numbers and still get some benefit from the data.
What is the significance of the variation in the number of entries these cells in each row—1, 2, 3, and 4 in rows 2 through 5, then 9 in each row following?
The employee in row one is the most senior and therefore only has to choose one number. The next employee must choose two, as her first choice may be taken by the more senior employee, and so on.
Please ignore the borders- they were created when I was trying to convert the source data from space delimited to tab delimited so each employee's schedule preference list would have one entry per cell vs. having the entire list in one cell.
Hi cnickflight,
I suspect the best approach to your situation will be a script. You could run it once when everyone has made their requests and be done. It could probably be designed to spit out a column with shift assignments and simplify the task of looking for highlights. While I am not your man, there are a couple of scriping gurus on this forum. Hopefully one will stop by.
quinn
A script is an efficient choice to solve this kind of problem:
Scripts are good and keeping track of lists and removing items as they go along.
No scripting knowledge needed to use this. Just:
This assumes you are using the first and second table on the sheet, as in your screenshot. You could also substitute the actual table names (surrounded by " ") where you see table 1 and table2 in the script.
You could then use Conditional Highlighting to color the original cells if you want, by comparing them to relevant cell in your paste column.
SG
tell application "Numbers"
tell front document's active sheet
set thePicks to table 1's rows'scells'sformatted value
set refList to table 2's column 1's cells's formatted value
end tell
end tell
set pasteStr to ""
repeat with i in thePicks's items
repeat with j in i's items
tell j's contents
if it is in refList then
set pasteStr to pasteStr & it & return
set refList to my removeOnce(it, refList)
exit repeat
end if
end tell
end repeat
end repeat
set the clipboard topasteStr
return pasteStr
to removeOnce(valToRemove, aList)
set {l, valsRemoved} to {{}, {}} -- start empty lists
repeat with i in aList
tell i's contents
if it is not valToRemove or it is in valsRemoved then
copy it to l's end
else
copy it to valsRemoved's end
end if
end tell
end repeat
return l
end removeOnce
Thanks for the reply, SGIII. What you posted is perfect, but I get this error when I run the script: error "Numbers got an error: Can’t get document 1." number -1728 from document 1
I got the same error last night when I was trying to format the data using a script that you had posted in another thread. Numbers is open and the appropriate sheet is the front document.
I can't reproduce that problem here. Are you able to tell which line is generating the error?
Some initial guesses, things to double-check:
Also, sometimes quitting Numbers and Script Editor, and then restarting the Mac can clear up strange problems.
SG
P.S. Also, though I can't test it, the solution for your problem may be in this thread.
An interesting question.
Like quinn's, my first impression was that it would take a script to solve due to the need to eliminate values, some of them repeated, one at a time. While waiting for the scripters to arrive, though, I took a stab at a solution using formulas and auxiliary tables. Here's what I came up with.
The first pair of auxiliary tables build a pool of items (schedule numbers) in a form that can then be drained using the choices made.
Pool filler, on the left, contains two columns (plus a third I added for my own interest to keep track of the length of the growing string that was to fill the pool).
Column A contains the list of schedule numbers. Note that the numbers less than 10 have a leading zero to make every number the same length. The cell format for all cells in this column is set to Text to preserve those leading zeros.
Column B uses a simple concatenation formula to build a space separated string listing all of the schedule numbers, including the multiple 10s and 15s.
The list in the final row of the table is transferred to cell B2 of the table Pool.
Formula: Poolbuilder::B3 (and filled down to the last row containing a schedule number, which is also the last row of the table): =B2&A3&" "
Note that the formula references cell B2, which must be left empty, which is the reason for using two header rows.
Formula: C3 and filled down: =LEN(B)
For my own interest while developing the table. NOT used in operation of the document. May be omitted.
Table "Pool"
For consistency in row numbering on the tables, I decided that all would have two header rows.
Formula: Pool::B2: INDEX(Pool filler::A:B,ROWS(Pool filler::A,0),2)
This copies the full string of schedule numbers from the last row of column B of Pool filler.
If the schedule number list will never, or very rarely, be changed, I would recommend copying the the contents of the last row of column B of Pool filler, then using Edit>Paste formula Results to pst the string into B2 of Pool.
A3 of Pool, and the cells below it, contain the schedule numbers assigned on the matching row of the "Assign" table (see next post). They are gathered with this formula:
Pool::A3, and filed down: =Assign::A3
B3, and cells below use the number in A to remove one copy of the assigned schedule number from the pool list in the cell above.
Pool::B3, and filled down: =REPLACE(B2,FIND(A,B2,1),3,"")
More below.
Barry
...continued.
The front end table, "Bids", and one more auxiliary table, "Assign" complete the solution.
Bids is your original table, on which the employees are listed, and their schedule choices are recorded.
There are no formulas on this table, but there is a conditional highlighting rule, set in cell C3, then filled into the rest of the cells below and to the right of that using the Combine choice in the conditional highlighting pane.
Assign captures the first available choice on each line, and transfers that choice to the same row of column A on the same table.
Assign::C3: =IFERROR(IF(AND(COUNT($B3:B3)<1,B=""),VALUE(MID(Pool::$B2,FIND(Bids::C3,Pool::$ B2),2)),""),"")
Two header columns are used here to provide a column in which to place the result, and an empty cell to the left of the initial copy of the formula.
IFERROR is to detect the 'can't find' that will occur if the value being searched for is not found in the version of the pool that this formula searches. If the error occurs, IFERROR places the last null string in the cell.
IF evaluates the AND statement. If one or both comparisons in AND return FALSE (COUNT finds a number in any cell in the specified range or the cell to the left of the formula is not 'empty'), the formula places the next to last null string in the cell.
If both comparisons are TRUE, AND returns TRUE, and the VALUE(MID(Find part of the formula is activated.
FIND gets the schedule number from the cell in the same opposition on Bids and the cell containing the formula on Assign (C3), searches for it in the string in B2 (one row above the row containing the formula), and returns the number of characters in from the beginning of the string that value starts.
MID takes that number from Find, and retrieves the two character 'number' beginning with the character at that location.
VALUE gets the two character string ( "10" ) from MID and converts it to a numeric value and places it as the result of the formula.
A3: =MAX(3:3)
MAX examines all the cells in row 3 of this table, skipping the cells in header columns, and returns the maximum (and only) number found.
On Pool, Cell A3 captures that number from A3 on this table (Assign) and the formula in Pool::B3 removes it from the string in the cell above it (Pool::B2.
On Bids, the conditional highlight rule in each cell of row 3 compares its number with the number in A3 of Assign, and applies an orange fill if they match.
Tested only as far as shown in these posts. If you can send me the list of schedule numbers (including repeats) and the full sample list of choices made (without including the names or employee numbers), I would like to test it with that full list.
My email address is available in my profile. Click my name at top left of this post, then click Profile in the window that opens.
Regards,
Barry
I find the script approach easier. But with Barry's solution as an inspiration (the use of strings to store and remove values from the list as they are chosen is brilliant!) I got this working here:
The 'Picks' table is the original list.
The 'Assignments' table has one big working formula in C2, filled right and down:
=IFERROR(IF(OR(Picks::C2="",COUNTIFS($B2:B2,"<>"&"")>1),"",MID(RefNums::$C2,FIN D(Picks::C2,RefNums::$C2),2)*1),"")
This leaves the cell blank if there is no corresponding value in the 'Picks' table or if a value has already been picked for this row (more than 1 non-blank cell from column B on). Otherwise, it checks the 'RefNums' table to see if the value in 'Picks' is still available there. If it is, that value is displayed. If not, FIND returns an error which IFERROR handles as a blank.
(This is broadly based on Barry's approach, and wouldn't have known how to do it without his example.)
'Assignments' has one more formula, in A2, filled down:
=MIN(2:2)
The 'RefNums' table has the original numbers down column A. The first number is also "seeded" into B2 by inputing it directly there. In B3, filled down, is:
=B2&","&A3
In C2:
=INDEX(B,ROWS(B)−ROW(cell)+2)
(This simply gets the "full list" of concatenated numbers from the bottom of column B. Remove any blank rows down there.)
In C3, filled down:
=SUBSTITUTE(C2,D2,"",1)
In D2, filled down:
=IFERROR(Assignments::A2,"")
To apply the Conditional Highlighting, I selected C2:K9 in 'Picks' and set this rule:
SG
highlight value from list
