Using a formula to populate a list of holiday dates from a work rota.

[Browneh89]

Hello,

I am attempting to write a formula that will list the dates someone has taken holidays from a work rota. I'm not 100% sure the best way to go about this in fact I'm not entirely sure if it's possible at all with the current layout of the rota.

I am thinking possibly an INDEX/MATCH formula with an IF statement but I could be thinking about this in the wrong way.

I'll attach two images to the post of the rota the list needs to pull the data from and what I would like the end game list to look like, would really appreciate if someone could point me in the right direction or if I'm wasting my time attempting to make this work.

I want the formula to be able to recognize the "H" in the row which corresponds with the person and reference the date above in the list.

I hope this all makes sense!

 

[Fluff]

Can you please post some sample data using the XL2BB add-in?

Also please update your account details to show which version of Excel you are using, as this can make a difference to which functions you can use.

 

[Browneh89]

Will do!

Also i have updated what you asked.

Struggles.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
2		01-Apr	02-Apr	03-Apr	04-Apr	05-Apr	06-Apr	07-Apr	08-Apr	09-Apr	10-Apr	11-Apr
3	BOB	H	H	H	7-15		7-15	7-15	7-15		7-15	7-15
4	JAMES	7-15	7-15	7-15		7-15	H	H	H	7-15
5	PAUL	14-22		H	H	H	14-22		14-22	14-22	14-22
6	HOLLY	H	22-6			22-6	22-6	H	H	H
ROTA

Struggles.xlsx
	A	B	C	D
1	BOB	JAMES	PAUL	HOLLY
2	01-Apr	06-Apr	03-Apr	01-Apr
3	02-Apr	07-Apr	04-Apr	07-Apr
4	03-Apr	08-Apr	05-Apr	08-Apr
5				09-Apr
6
7
8
9
10
11
12
13
How it should look

 

[Browneh89]

Sorry, I should have explained.. The top table is the rota I need to pull the data from and the bottom table is how I want it to look.

I want the 2nd table to detect the cells with the letter "H" in them and reference the date above for each row

 

[Fluff]

Thanks for that, how about

+Fluff New.xlsm
	A	B	C	D
1	BOB	JAMES	PAUL	HOLLY
2	01/04/2020	06/04/2020	03/04/2020	01/04/2020
3	02/04/2020	07/04/2020	04/04/2020	07/04/2020
4	03/04/2020	08/04/2020	05/04/2020	08/04/2020
5				09/04/2020
6
7
Main

Cell Formulas
Range	Formula
A2:D6	A2	=IFERROR(INDEX(Rota!$B$2:$L$2,AGGREGATE(15,6,(COLUMN(Rota!$B$2:$L$2)-COLUMN(Rota!$B$2)+1)/((Rota!$A$3:$A$6=A$1)*(Rota!$B$3:$L$6="H")),ROWS(A$2:A2))),"")

 

[Browneh89]

Woah this is fantastic and it works perfectly as far as I can see!

This is far in advance of stuff I typically use. I can adjust the formula to suit my needs but do you think you could break it down for me so I can better understand it in case I ever want to use something like this again?

Again thank you very much!

 

[Fluff]

This part
(COLUMN(Rota!$B$2:$L$2)-COLUMN(Rota!$B$2)+1)
returns an array of numbers
{1,2,3,4,5,6,7,8,9,10,11}
This
((Rota!$A$3:$A$6=A$1)*(Rota!$B$3:$L$6="H"))
Checks if the values in A3:A6 match A1 & the values in B3:L6 are H & returns an array of 1 or 0 (true or false)
When the 1st array is divided by the 2nd array you get
{1,2,3,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!;#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!;#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!;#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!}

The argument 15 in the aggregate function works the same as the small function, so it find the kth smallest number & the argument 6 tells it to ignore errors
This part
ROWS(A$2:A2)
will return 1 for the first cell, which in turn tells the aggregate to return the 1st smallest number from the array, when it's dragged down to the next row
ROWS(A$2:A2) becomes ROWS(A$2:A3)
which returns 2 & so the aggregate returns the 2nd smallest number from the array, etc.
These numbers are then used in the index function to return the relevant cell.

HTH