CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Anomaly Ridden
Anomaly Ridden

Reputation: 1

Need a table to auto-pop based on availability table and positon without dupe results. Also need it to populate a substitute list when main is full

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

Upvotes: 0

Views: 49

Answers (1)

Anomaly Ridden
Anomaly Ridden

Reputation: 1

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.

Upvotes: 0

Related Questions