Leave roster with duplicate names and multiple date ranges

[harleythecav]

Hello forum - not sure what is possible so any assistance with the below question appreciated.

I have 2 worksheets in a workbook: Sheet 1 is my raw data set and Sheet 2 is the leave roster I'm creating.

Sheet 1 (raw data) has:
Column D = names (duplicate names exist)
Column F = Status (6 options: Approved, Unapproved, Cancelled, Declined, Deleted, Errored)
Column G = start date
Column H = end date

Sheet 2 (leave roster) has:
Column B = names (lists of names from sheet 1 with no duplicates using index/match formula to create list of unique values - this starts at row 13, header row 12)
Row 12 is the dates starting with =today() in Column F (and this continues across the row for 365 columns to capture 1 year using +1)

Ideally what I would like to do is:
Bring back the Status from Column F in Sheet 1 into Sheet 2, starting at F13 (then below and across for all names and dates) based on if -
1) the date in Sheet 2 Row 12 (starting with Column F) is equal to or between the date range of Columns G and H in Sheet 1
and
2) the name in Sheet 2 Column B (starting with Row 13) matches the name in Sheet 1 Column D (keeping in mind that Sheet 1 has duplicate names and Sheet 2 uses a formula to create a unique list of names that will change when the raw data in Sheet 1 is updated)

Ideally I would like to capture the Status as an abbreviation if possible (Approved = A, Unapproved = U, Cancelled = Ca etc) however if only Approved and Unapproved are possible this would be enough to help with blank or "X" where no data point exists.

I know how to achieve this is there are no duplicates names in Sheet 1 and also if I use an interim table as a work around but I don't want to do that if I can avoid it.
Is what I want to achieve possible?

Sheet 1 (Range A3 to H8):

Mgr Name	Mgr Desc	Employee Code	Employee Name	Request Length	Status	Start Date	End Date
		1	Name 1		Approved	30/03/2020	30/03/2020
		2	Name 1		Deleted	6/04/2020	17/04/2020
		3	Name 2		Unapproved	24/04/2020	15/05/2020
		4	Name 3		Approved	14/04/2020	17/04/2020
		5	Name 4		Cancelled	26/03/2020	27/03/2020

Sheet 2 (Range B12 to J17) - illustrative example only of what I am trying to achieve, does not directly match the data example shown above:

Employee Name	Payroll #	Mgr Name	Mgr Desc	19-May	20-May	21-May	22-May	23-May
Name 1	1	-	-	X	X	A	A	A
Name 2	3	-	-	X	X	X	X	X
Name 3	4	-	-	U	X	X	X	X
Name 4	5	-	-	X	X	Ca	X	X
Name 5	7	-	-	X	Del	X	X	X

 

[Toadstool]

Hi HarleyTheCav,

I've changed some date ranges to get better test data, added a lookup table to decode the Status into a Display Code. Beware of using my test data as the dates will be in US format.

HarleyTheCav.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
3	Mgr Name	Mgr Desc	Employee Code	Employee Name	Request Length	Status	Start Date	End Date				Status	Display Code
4			1	Name 1		Approved	30-Mar-20	30-Mar-20				Approved	A
5			2	Name 1		Deleted	06-Apr-20	17-Apr-20				Unapproved	U
6			3	Name 2		Unapproved	24-Apr-20	22-May-20				Cancelled	Ca
7			4	Name 3		Approved	20-May-20	21-May-20				Declined	Dec
8			5	Name 4		Cancelled	22-May-20	23-May-20				Deleted	Del
9			7	Name 5		Declined	20-May-20	20-May-20				Errored	Err
10												None	X
Sheet1

Here's Sheet2 with the formulae.

HarleyTheCav.xlsx
	B	C	D	E	F	G	H	I	J
12	Employee Name	Payroll #	Mgr Name	Mgr Desc	19-May-20	20-May-20	21-May-20	22-May-20	23-May-20
13	Name 1	1	-	-	X	X	X	X	X
14	Name 2	3	-	-	U	U	U	U	X
15	Name 3	4	-	-	X	A	A	X	X
16	Name 4	5	-	-	X	X	X	Ca	Ca
17	Name 5	7	-	-	X	Dec	X	X	X
Sheet2

Cell Formulas
Range	Formula
F12	F12	=TODAY()
G12:J12	G12	=F12+1
F13:J17	F13	=IF(B13="","",INDEX(Sheet1!$M$4:$M$10,MATCH(IFERROR(INDEX(Sheet1!$F$4:$F$11,AGGREGATE(15,6,ROW(Sheet1!$D$4:$D$11)-ROW(Sheet1!$D$3)/((Sheet1!$G$4:$G$11<=F$12)*(Sheet1!$H$4:$H$11>=F$12)*(Sheet1!$D$4:$D$11=$B13)),1)),"None"),Sheet1!$L$4:$L$10,0)))
B13:B16	B13	=INDEX(Sheet1!$D$4:$D$11,MATCH(0,INDEX(COUNTIF($B$12:$B12,Sheet1!$D$4:$D$11),),0))&""
C13:C17	C13	=IFERROR(INDEX(Sheet1!$C$4:$C$11,MATCH(B13,Sheet1!$D$4:$D$11,0)),"")

 

[harleythecav]

This solution worked perfectly, thank you so much for your prompt response!
Also, the formula you used for the unique list of names had a slight variation to the one I was using so I now no longer get a random 0 at the end of my list which is a bonus
Sincerely appreciate your time and expertise

 

[Toadstool]

You're welcome!