Fill down excel values between dates

Question

I have an excel sheet (called Sheet1) with rows of data that look like this:

 Unit | Names  | Begin_Date | End_Date
-----------------------------------
  A   | Jones  | 1/1/2016   | 1/4/2018
  A   | Frank  | 2/11/2018  | 
  B   | Adam   | 3/5/2011   | 
  C   | Jane   | 6/9/2012   | 7/14/2016
  C   | John   | 7/28/2016  | 9/22/2017
  C   | Joe    | 12/31/2017 | 1/1/2019
  C   | Joe    | 1/2/2019   |

I am trying to get it into a format that has the units as column headers and dates as rows. The idea is that for every day between the begin and end dates, the name of the person should be in the appropriate cell. If there is a gap between the end date of one person and the begin date of the next within the same unit, the formula will list "vacant". It is assumed each unit was vacant before the first "begin date" for that unit, and blanks for end dates mean that the person still occupies that unit. Ideally the completed data set would look like this, on Sheet2:

  Date     |   A    |   B   |   C   |
 -------------------------------------
 3/5/2011  | Vacant | Adam  | Vacant
 3/6/2011  | Vacant | Adam  | Vacant
   ...
 6/9/2012  | Vacant | Adam  | Jane
   ...
 1/1/2016  | Jones  | Adam  | Jane
   ...
 7/14/2016 | Jones  | Adam  | Jane
 7/15/2016 | Jones  | Adam  | Vacant
 7/16/2016 | Jones  | Adam  | Vacant
   ...
 7/28/2016 | Jones  | Adam  | John
  ...
 1/4/2018  | Jones  | Adam  | Joe
 1/5/2018  | Vacant | Adam  | Joe

etc.

The formula I have thus far populates the first values, i.e. the first person to live in the unit or, if no one, then it lists "vacant" on Sheet2. However, I am not sure how to extend it to look for the next person. I've listed all the dates in column A and all of the unique Unit names in row 1. Any help or advice would be appreciated!

=iferror(if(index(Sheet1!Names,match(1,(Sheet2!A$2=Sheet1!Begin_Date)*(Sheet2!$A3=Sheet1!End_Date),0))=Index(Sheet1!Names,match($A2,ArrayFormula(min(Sheet1!Unit=B$2,Sheet1!Begin_Date)),"Vacant",Index(Sheet1!Names,match($A2,ArrayFormula(min(Sheet1!Unit=B$2,Sheet1!Begin_Date))),Index(Sheet1!Names,match($A2,ArrayFormula(min(Sheet1!Unit=B$2,Sheet1!Begin_Date)))

Tom Sharpe Tom Sharpe · Accepted Answer · 2019-07-27T09:14:24

This uses aggregate and is basically an array formula but without having to be entered with CtrlShiftEnter

=IF($F2="","",IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$2:$A$10)/
(($A$2:$A$10=G$1)*($F2>=$C$2:$C$10)*(($F2<=$D$2:$D$10)+($D$2:$D$10=""))),1)),"Vacant"))

Aggregate(15,6...1) woks out the minimum value of the array inside it, ignoring any error values. This array consists of the rows 2-10 divided by the conditions on the rows. The conditions are set up (as you did) using * for AND and + for OR. Where the conditions are false, this leads to a division by zero which gives an error, so only the rows which satisfy the condition are taken into account.

You can also use this

=IF($F2="","",IFERROR(INDEX($B$2:$B$10,MATCH(1,
($A$2:$A$10=G$1)*($F2>=$C$2:$C$10)*(($F2<=$D$2:$D$10)+($D$2:$D$10="")),0)),"Vacant"))

entered as an array formula.

Fill down excel values between dates

1 Answers