Dynamic list of unique values based on dates range

[Yhadax]

Hi all!

I need help with a formula, would be great if someone could help

My employees data base is in an excel sheet, they work in different projects every day so their time-sheet is split in entries per project.
The idea is to extract who worked during a certain period of time to calculate their payroll - every 2 weeks, dynamically.

The way I have it organized at the moment is the following:

Sheet 1
Data base - only unique values with the names of all my employees in column B and some extra data

Sheet 2
All the time-sheet entries per employee/per day.
Column B = Date of work
Column C = Name of the employee

Sheet 3
A year calendar and the hours calculator, will enter the dates manually every 2 weeks.
B6 = Start date
C6 = End date

I've seen several Arrays formulas online but I don't get to trigger what I need, could you help me please? Do you need any more data to help you understand what I need?

 

[RasGhul]

So the goal would be to choose an employee and the date range and the formula would return the relevant data?

Can you post some sample data?

 

[Yhadax]

The idea is to select a Start and End date and get a list of employees, that will change dynamically as we change the dates.

[TABLE="width: 166"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Start date[/TD]
[TD="align: right"]2019-02-08[/TD]
[/TR]
[TR]
[TD]End date[/TD]
[TD="align: right"]2019-02-11
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 166"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Employee[/TD]
[/TR]
[TR]
[TD="align: right"]02-04 [/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD="align: right"]02-05 [/TD]
[TD]Jason[/TD]
[/TR]
[TR]
[TD="align: right"]02-06 [/TD]
[TD]Pamela[/TD]
[/TR]
[TR]
[TD="align: right"]02-07 [/TD]
[TD]Pamela[/TD]
[/TR]
[TR]
[TD="align: right"]02-08 [/TD]
[TD]Sarah[/TD]
[/TR]
[TR]
[TD="align: right"]02-09 [/TD]
[TD]Mario[/TD]
[/TR]
[TR]
[TD="align: right"]02-10 [/TD]
[TD]Jason[/TD]
[/TR]
[TR]
[TD="align: right"]02-10 [/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD="align: right"]02-10 [/TD]
[TD]Pamela[/TD]
[/TR]
[TR]
[TD="align: right"]02-10 [/TD]
[TD]Sarah[/TD]
[/TR]
[TR]
[TD="align: right"]02-10 [/TD]
[TD]Pamela[/TD]
[/TR]
[TR]
[TD="align: right"]02-11 [/TD]
[TD]Sarah[/TD]
[/TR]
[TR]
[TD="align: right"]02-12 [/TD]
[TD]Mario[/TD]
[/TR]
[TR]
[TD="align: right"]02-13 [/TD]
[TD]Jason[/TD]
[/TR]
[TR]
[TD="align: right"]02-14 [/TD]
[TD]John[/TD]
[/TR]
</tbody>[/TABLE]

List of employees worked during the period above (the list updates if the date range changes):

[TABLE="width: 113"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Sarah[/TD]
[/TR]
[TR]
[TD]Mario[/TD]
[/TR]
[TR]
[TD]Jason[/TD]
[/TR]
[TR]
[TD]John[/TD]
[/TR]
[TR]
[TD]Pamela[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

What is 02-04? 4-Feb-2019?

 

[Yhadax]

correct!

 

[Aladin Akyurek]

Book1
	A	B	C	D
1	Start date	2/8/2019		emps worked
2	End date	2/11/2019		John
3				Jason
4				Pamela
5	Date	Employee		Sarah
6	2/4/2019	John		Mario
7	2/5/2019	Jason
8	2/6/2019	Pamela
9	2/7/2019	Pamela
10	2/8/2019	Sarah
11	2/9/2019	Mario
12	2/10/2019	Jason
13	2/10/2019	John
14	2/10/2019	Pamela
15	2/10/2019	Sarah
16	2/10/2019	Pamela
17	2/11/2019	Sarah
18	2/12/2019	Mario
19	2/13/2019	Jason
20	2/14/2019	John
Sheet1

In D2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$6:$B$20,SMALL(IF(FREQUENCY(IF($B$6:$B$20<>"",IF($A$6:$A$20>=$B$1,IF($A$6:$A$20<=$B$2,MATCH($B$6:$B$20,$B$6:$B$20,0)))),ROW($B$6:$B$20)-ROW(INDEX($B$6:$B$20,1,1))+1),ROW($B$6:$B$20)-ROW(INDEX($B$6:$B$20,1,1))+1),ROWS($D$2:D2))),"")

Using named ranges...

=IFERROR(INDEX(employee,SMALL(IF(FREQUENCY(IF(employee<>"",IF(date>=$B$1,IF(date<=$B$2,MATCH(employee,employee,0)))),ivec),ivec),ROWS($D$2:D2))),"")

where ivec is defined in the Name Manager as referring to:

=ROW(employee)-ROW(INDEX(employee,1,1))+1