Thank you in advance for your time and any help you can provide.
What I am using: Google Drive – Google Sheets
Current Skill level: Beginner-Intermidiate
I am currently trying to make a table that auto-populates based on an availability table without having duplicate names in the table. I also want for it to auto-populate the substitute section when the main part is filled out that then changes the “sub” position to their 1st position. If possible wouls it also be possible for it to put them in the sub position if one of their three positions are not available ?
I would prefer not to have to code and use a formula but if that is not possible then so be it I guess. Below is a picture of the current set up, all data is place holder.
Table on left to be populated, table on right availability table
What I have tried:
I have tried using Vlookup with helpers to find people with the required position and availability checked to true, if it can’t find one in Position 1 it will then move to position 2, then to 3. Problem is that it gives duplicate names for some positions.
I considered making use of a third table “Selected” but I’m not currently sure how it would work without 117-120 if statements. I do not know how iterative formulas work or if they would work in this situation.
Current formulas in use:
Auto-populate table uses this formula
=IF(ISNA(VLOOKUP($C2&TRUE, $E2:$M40,7,FALSE)), IF(ISNA(VLOOKUP($C2&TRUE,$G2:$M40,5,FALSE)), IF(ISNA(VLOOKUP($C2&TRUE, $I2:$M40,3,FALSE)), Positions!$B$2, VLOOKUP($C2&TRUE, $I2:$M40,3,FALSE)), VLOOKUP($C2&TRUE,$G2:$M40,5,FALSE)), VLOOKUP($C2&TRUE, $E2:$M40,7,FALSE))
Edit 07/18/2024
Example sheet with realistic placeholder data, appologies if this link doesn’t work.
https://docs.google.com/spreadsheets/d/1D2SD-aU_Rre_W4zhxG0gj3CTNiRZuhD02aqwEG3OM5Y/edit?usp=sharing
Anomaly Ridden is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
4
Thank you for the help that I recieved on the Google Sheet, whilst I don’t know how it works it does seem to work.
The formula used was as such, using a timestamp created when the availability is checked in the K column.
I then made a formual that checked which checked person was in the auto-poped table and those that weren’t were assumed to be substitues and added to the list underneath.
=let(Σ,tocol(,1), Λ,reduce(Σ,sort(tocol(K2:K40,1),1,),lambda(a,c,vstack(if(iserr(+a),Σ,a), let(x,vstack(xlookup(c,K:K,{E:E,H:I}),xlookup(c,K:K,{F:F,H:I}),xlookup(c,K:K,{G:G,H:I})),chooserows(filter(x,isna(xmatch(index(x,,1),index(a,,1)))),1))))), map(C2:C16,lambda(Σ,iferror(vlookup(Σ,Λ,{2,3},)))))
Thank you very much to everyone who took the time to read my question and provide help both here and on the sheet.
Anomaly Ridden is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.