Schedule tracker - just need to return a yes if anyone of multiple people can do time/day

[leon_mc]

Hi,

Any help is appreciated. I have attached some sample data.

From Columns A to P, i have a list of dates and then a timetable where various people have advised they can be available at certain time blocks. Each different row on the same date represents a different person that has given their availability for that day. This is collapsed from a much larger list, the unique names aren't important, just the dates and time availability on that date are needed.

I wish to consolidate this information into one master row per day and collapse all the 'Yes' responses into a single row format. So as long as anyone one of the people have indicated that on that day, at that specific time, they can be available then the master/consolidated row should be marked as 'Yes' on that day, at that time. If no one has selected 'yes' on that day, at that time, then no result should appear on the master/consolidated row.

I am looking for this result to be entered into rows T to AH.

I have included a sample row as an example - T4:AH4.

It seems pretty simple, I just haven't had to consolidate data in this fashion previously so am at a loss as to how to proceed on this.

Thanks in advance!

 

[J.Ty.]

Hi,

I have made the following solution. You can test it here.

leon_mc.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Days	8	9	10	11	12	13		Days	8	9	10	11	12	13
2	1	Yes	Yes			Yes			1	Yes	Yes			Yes
3	1								2	Yes		Yes
4	1	Yes							3
5	1								4
6	2
7	2	Yes
8	2			Yes
9	3
10	3
11	3
12	3
13	4
14	4
Sheet1

Cell Formulas
Range	Formula
J2:O5	J2	=IF(SUMPRODUCT(($B$2:$G$16="Yes")*($A$2:$A$16=$I2)*($B$1:$G$1=J$1)),"Yes","")

 

[leon_mc]

That's perfect thank you!

 

[J.Ty.]

Thanks for the feedback and good luck!

J.Ty.