# Problem making a spreadsheet schedule based on the availability of two items.

• 04-25-2019, 01:51 PM
timothysklugh
Problem making a spreadsheet schedule based on the availability of two items.
For some reason, the logic behind the spreadsheet schedule I am making is very confusing to translate to the spreadsheet. I am going to be servicing computers for about a hundred users, while their computers are being serviced, they will be borrowing one of two loaner computers. The normal stretch for servicing the computers is three days, but it could be shorter or longer. The two loaner computers get loaned out based on what loaner computer is available when that user brings in their computer.

I am making a schedule that it like the following:

Col A: User Name
Col B: Date user is to bring in their computer
Col C: Date user is intended to pick up their computer
Col D: What loaner computer in to be given to that user to use in the meantime (Loaner 1 or Loaner 2)

Say the list look like this

Row 1: Johnny Apple, 05/01/2019, 05/03/2019, Loaner 1
Row 2: Kevin Bicycle, 05/01/2019, 05/03/2019, Loaner 2
Row 3: Larry Cat, 05/04/2019, 05/07/2019, Loaner 1
Row 4: Marvin Dot, 05/04/2019, 05/07/2019, Loaner 2
Row 5: Nancy Egg, 05/08/2019, 05/11/2019, Loaner 1
Row 6: Ollie Fawn, 05/08/2019, 05/11/2019, Loaner 2
Row 7: Pat Green, 05/12/2019, 05/15/2019, Loaner 1
Row 8: Randy Holly, 05/12/2019, 05/15/2019, Loaner 2

Now, without any needed alterations to the schedule, the schedule seems obvious and predictable. However, I have to allow for a situation like, say, Larry Cat's computer actually ends up taking 7 days to service, so the full three days are extended by four. That means that the following alternation to the schedule would automatically recalculate to:

Row 1: Johnny Apple, 05/01/2019, 05/03/2019, Loaner 1
Row 2: Kevin Bicycle, 05/01/2019, 05/03/2019, Loaner 2
Row 3: Larry Cat, 05/04/2019, 05/11/2019, Loaner 1
Row 4: Marvin Dot, 05/04/2019, 05/07/2019, Loaner 2
Row 5: Nancy Egg, 05/08/2019, 05/11/2019, Loaner 2
Row 6: Ollie Fawn, 05/12/2019, 05/15/2019, Loaner 1
Row 7: Pat Green, 05/12/2019, 05/15/2019, Loaner 2
Row 8: Randy Holly, 05/16/2019, 05/19/2019, Loaner 1

Can someone help this novice work out this logic is Excel? I cannot seem to work out how I would get a spreadsheet to work out this logic?

Timothy
• 05-02-2019, 03:03 PM
JeteMc
Re: Problem making a spreadsheet schedule based on the availability of two items.
Hello timothysklugh and Welcome to Excel Forum.
Perhaps the following will help.
The first two dates when the client is expected to bring their computer in are manually filled then the following formula fills rows 4 and down:
Formula:
`=IFERROR(MAX(INDEX(C\$2:C\$9,MATCH(ROWS(A\$1:A1),F\$2:F\$9,0))+1,INDEX(D\$2:D\$9,MATCH(ROWS(A\$1:A1),F\$2:F\$9,0))+1),"")`

The date on which the computer is expected to go back out to the client is filled using: =MIN(D2,SUM(B2,3))
The actual dates on which the computer goes back out to the client need to be manually filled.
The first two rows of loaner computer need to be manually filled then, starting with row 4, use: =IFERROR(INDEX(E\$2:E3,MATCH(ROWS(A\$1:A1),F\$2:F3,0)),"")
The Rank column, which may be moved and/or hidden for aesthetic purposes uses:
Formula:
`=IF(D2<>"",SUM(IFERROR(RANK(D2,D\$2:D\$9,1),COUNTIF(D\$2:D\$9,">0")+1),COUNTIF(D\$2:D2,D2)-1),SUM(MAX(F\$1:F1),1))`

Let us know if you have any questions.
• 05-03-2019, 08:31 AM
timothysklugh
Re: Problem making a spreadsheet schedule based on the availability of two items.
Thank you for responding! :) I will take a look.

Timothy