Google sheet formula assistance

[jwoww]

I am working on a personal project that, long story short, involves planning aircraft arrival times at a fictional airport that i am tracking through Google Sheets. i have made a smaller sized version of the spreadsheet that only includes the important information for ease of reading

Aircraft parking efficiency

"Arrivals Sheet" will be where "Timing Sheet" gets its information for column E

basically all aircraft will park at either parking bay 1, 2, 3, 4 (indicated in the "Timing Sheet" under column D) and given there are 58 aircraft in my example they can't all use the 4 assigned parking bays at once.

on "Arrivals Sheet", under column G, i want the value in column G for each row to be based on when that specific parking bay (based on the number in column F) is next available from, as per the value in Column H on "Timing Sheet" for when that stand becomes available from the last time it was used

ie the aircraft from India will park in the same parking bay as the aircraft from Mauritius (bay 3) so will be available from the time that the Mauritius aircraft is towed to unloaded (column H) in "Timing Sheet" ie 13:54
the aircraft from Botswana will park in the same parking bay as the aircraft from Lesotho (bay 1) so will be available from the time that the Lesotho aircraft is towed to unloaded (column H) in "Timing Sheet" ie 14:40

the idea is that i update the departure times on "Arrivals Sheet" which in turn adjusts the Aircraft Landing Time on "Timing Sheet" which is based on the Arrival time for "Arrivals Sheet" to make sure that parking bays are available during landing times and resources are being used effectively.

i tried getting AI to help me but it kept giving me an error result. grateful for any help

 

[coulombevin]

Hi jwoww,

Here's what I would do:

=IF($E2=MIN($E$2:$E$40),1,LAMBDA(tbl,IF(CHOOSEROWS(tbl,1)="---","NO BAY LEFT",CHOOSEROWS(TRANSPOSE(tbl),RANDBETWEEN(1,ROWS(tbl)))))(SPLIT(REDUCE("1-2-3-4",IFERROR(FILTER($D1:$D$2,$E2<=$H1:$H$2,ISNUMBER($D1:$D$2)),""),LAMBDA(c,v,SUBSTITUE(c,v,""))),"-",TRUE,TURE)))

Honestly, I would try to find a way around and delay flight when no bay left with a + x mins to go around the airport instead of a "NO BAY LEFT". Only if you need to we could look into it.

Bests regards,

Vincent

 

[jwoww]

Hi jwoww,

Here's what I would do:

=IF($E2=MIN($E$2:$E$40),1,LAMBDA(tbl,IF(CHOOSEROWS(tbl,1)="---","NO BAY LEFT",CHOOSEROWS(TRANSPOSE(tbl),RANDBETWEEN(1,ROWS(tbl)))))(SPLIT(REDUCE("1-2-3-4",IFERROR(FILTER($D1:$D$2,$E2<=$H1:$H$2,ISNUMBER($D1:$D$2)),""),LAMBDA(c,v,SUBSTITUE(c,v,""))),"-",TRUE,TURE)))

Honestly, I would try to find a way around and delay flight when no bay left with a + x mins to go around the airport instead of a "NO BAY LEFT". Only if you need to we could look into it.

Bests regards,

Vincent

Thanks for your help.

I've realised that i didn't explain myself properly.

i'm looking to, in column G of "Arrivals Sheet" have the result tell me what time the specific parking bay (as outlined in column F of the same row) is next available. This would be the tow to unload time listed on the "Timing Sheet" for that specific parking bay and the aircraft that used it last moving.

Sorry for any confusion

 

[coulombevin]

Hi jwow,

Try this in cell 2 of column "Stand available from" then drag down:

=LAMBDA(tbl,IF(CHOOSEROWS(tbl,1)="",'Timing Sheet'!$I2,CHOOSEROWS(tbl,ROWS(tbl))))(IFERROR(FILTER('Timing Sheet'!$I$1:$I1,'Timing Sheet'!$D$1:$D1=$E2,ISNUMBER('Timing Sheet'!$D$1:$D1)),""))

The code I previously wrote was for the column "Parking Bay" of sheet "Timing Sheet". That way you will prevent unnecessary waiting time by automaticaly/randomly giving a free bay to the plane:

=IF($E2=MIN($E$2:$E$40),1,LAMBDA(tbl,IF(CHOOSEROWS(tbl,1)="---","NO BAY LEFT",CHOOSEROWS(TRANSPOSE(tbl),RANDBETWEEN(1,ROWS(tbl)))))(SPLIT(REDUCE("1-2-3-4",IFERROR(FILTER($D1:$D$2,$E2<=$H1:$H$2,ISNUMBER($D1:$D$2)),""),LAMBDA(c,v,SUBSTITUE(c,v,""))),"-",TRUE,TURE)))

Bests regards,

Vincent