List all staff names that worked under a manager on a certain date

[surkdidat]

I have a list of 20,000 employs with a store number and their join and end date.

On another sheet I have a list of managers, with their end date.

What I need to do is have a spreadsheet that will list the managers name, with and end date, and I need to know every member of staff that worked there on their last day.

So, on the date the manager leaves, the staff member must have started that day or before, and still be at the store, or have left on or after the day the manager leaves.

The results could turn up 1 employee or 20 employees.

I hope my example in output makes sense (and the dates match up as they should do!!)

Employee List

A1- Employee Number	B1 - Employee Name	C1 - Store Number	D1 - Start Date	E1 - End Date
001	Richard	1000	31/01/2024	31/12/9999
002	James	1000	01/05/2024	31/12/9999
003	Jeremy	1000	28/01/2024	05/06/2024
004	Steve	1000	04/02/2023	12/12/2023
005	Katie	1001	01/01/2023	31/12/9999
006	Olivia	1002	01/02/2023	28/02/2023
007	Laila	1002	14/03/2024	31/12/9999
008	Ellie	1002	01/01/2023	14/03/2023
009	Jimmy	1002	01/01/2023	16/03/2023

Output

A1 - Manager Name	B1 - Manager Store	C1 - Manager Leave Date	D1 - Employee 1	E1 - Employee 2	F1 - Employee 3	G1 - Employee 4
Andy	1000	02/05/2024	Richard	James	Jeremy
Lee	1002	15/03/2023	Laila	Jimmy

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	A1 - Manager Name	B1 - Manager Store	C1 - Manager Leave Date	D1 - Employee 1	E1 - Employee 2	F1 - Employee 3	G1 - Employee 4
2	Andy	1000	02/05/2024	Richard	James	Jeremy
3	Lee	1002	15/03/2023	Jimmy
Sheet1

Cell Formulas
Range	Formula
D2:F2,D3	D2	=TOROW(FILTER(Sheet2!$B$2:$B$100,(Sheet2!$D$2:$D$100<=C2)*(Sheet2!$E$2:$E$100>=C2)*(Sheet2!$C$2:$C$100=B2)))
Dynamic array formulas.

 

[surkdidat]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	A1 - Manager Name	B1 - Manager Store	C1 - Manager Leave Date	D1 - Employee 1	E1 - Employee 2	F1 - Employee 3	G1 - Employee 4
2	Andy	1000	02/05/2024	Richard	James	Jeremy
3	Lee	1002	15/03/2023	Jimmy
Sheet1

Cell Formulas
Range	Formula
D2:F2,D3	D2	=TOROW(FILTER(Sheet2!$B$2:$B$100,(Sheet2!$D$2:$D$100<=C2)*(Sheet2!$E$2:$E$100>=C2)*(Sheet2!$C$2:$C$100=B2)))
Dynamic array formulas.

Thanks Fluff, that works perfectly with clean data. One thing this has thrown up in the data quality is there are records where the same manager could be listed as "Store Manager" or "Area Manager" for example, - is there a way of adapting the above formula if the name already appears in that row to skip it? (If not, don't worry, I can find a manual workaround (as the manager types are different, "Remove duplicates" doesn't work 100%, as the manager could also be at a different store number, so cannot do it by name alone, or by manager and title.....

 

[Fluff]

Can you provide some sample data.

 

[surkdidat]

Apologies, I got the details the wrong way around. What I meant is in the below table "Richard Jones" has two titles as the same store "Assistant" and "General assistant". All other details are the same, as its the same employee, he has just been set up twice. This obviously needs to be corrected int he data side, but can the formula be adapted so where it sees "Richard Jones" as employee 001 at store 1000 (ie all other details suggest its a duplicate record, his second record is skipped and so in the output table below Richard only shows up once. No biggie if not.

Employee List

A1- Employee Number	B1 - Employee Name	C1 - Position	D1 - Store Number	E1 - Start Date	F1 - End Date
001	Richard Jones	Assistant	1000	31/01/2024	31/12/9999
001	Richard Jones	General assistant	1000	31/01/2024	31/12/9999
002	James	Assistant	1000	01/05/2024	31/12/9999
003	Jeremy	Assistant	1000	28/01/2024	05/06/2024
004	Steve	Assistant	1000	04/02/2023	12/12/2023
005	Katie	Assistant	1001	01/01/2023	31/12/9999
006	Olivia	Assistant	1002	01/02/2023	28/02/2023
007	Laila	Assistant	1002	14/03/2024	31/12/9999
008	Ellie	Assistant	1002	01/01/2023	14/03/2023
009	Jimmy	Assistant	1002	01/01/2023	16/03/2023

Output

A1 - Manager Name	B1 - Manager Store	C1 - Manager Leave Date	D1 - Employee 1	E1 - Employee 2	F1 - Employee 3	G1 - Employee 4
Andy	1000	02/05/2024	Richard Jones	Richard Jones	James	Jeremy
Lee	1002	15/03/2023	Laila	Jimmy

 

[Fluff]

How about

=TOROW(UNIQUE(FILTER(Sheet2!$B$2:$B$100,(Sheet2!$D$2:$D$100<=C2)*(Sheet2!$E$2:$E$100>=C2)*(Sheet2!$C$2:$C$100=B2))))