Calendar/Scheduling Sheet

[mooshoo]

I manage a small business and have been asked to summarize a calendar we have in Excel. Basically we clean buildings every quarter - 1x a year is a full cleaning, 2x are just outside, and 1x sweep/vacuum but not full cleaning --> full and sweep can't be right after each other. We schedule out about 2 years but things change regularly. Each person has like 20-25 buildings. Below is a short example of one year.

F = full, O = outside, S = sweep

Location

Cleaner

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Building 1

Adam

F

O

S

O

Building 2

Brenda

F

O

S

O

Building 3

Charlie

F

O

S

O

Building 4

Denise

F

O

S

O

Building 5

Edward

O

F

O

S

Building 6

Brenda

F

O

S

O

Building 7

Brenda

O

F

O

S

Building 8

Denise

O

F

O

S

Building 9

Adam

F

O

S

O

<tbody>
</tbody>

I'm not an Excel pro by any means but took some courses in college years ago and remember a little. Is there a way to summarize these by month and employee?

I have a new sheet where I made a drop down for Employee and Month but I can't seem to find a way to summarize them nicely. Here is what I've been asked for so that the owner can track employees and may check in on them.

Employee	Brenda	<-- dropdown
Month	March	<-- dropdown
Full	Outside	Sweep Only
Building 6	Building 7	Building 9
Building 13	Building 10	Building 14
Building 16	Building 12	Building 25
Building 22	Building 15	Building 27
Building 23	Building 17	Building 28
...	...	...

<tbody>
</tbody>

I've tried a bunch of things but without success - pivot tables, combinations of giant if statements using index/match functions, arrays

Basically if Cleaner = Brenda and Month = March and any cell within those parameters = F, then list the names of those locations.

Can anyone help here? Or is it too difficult for a more novice user like me to do in Excel? Any help would be appreciated - thanks.

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​
1​
2​	Location	Cleaner	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3​	Building 1	Adam	F			O			S			O
4​	Building 2	Brenda		F			O			S			O
5​	Building 3	Charlie			F			O			S			O
6​	Building 4	Denise	F			O			S			O
7​	Building 5	Edward	O			F			O			S
8​	Building 6	Brenda			F			O			S			O
9​	Building 7	Brenda			O			F			O			S
10​	Building 8	Denise	O			F			O			S
11​	Building 9	Adam		F			O			S			O
12​
13​
14​	Employee		Brenda			<-- dropdown
15​	Month		Mar			<-- dropdown
16​
17​	Full	Outside	Sweep Only
18​	Building 6	Building 7	#NUM!​
19​	#NUM!​	#NUM!​
20​

Sheet: Sheet17

Array formula in cell A18:
=INDEX($A$3:$A$11,SMALL(IF(($C$3:$N$11="F")*($B$3:$B$11=$C$14)*($C$2:$N$2=$C$15),MATCH(ROW($A$3:$A$11),ROW($A$3:$A$11)),""),ROWS($A$1:A1)))

Array formula in cell B18:
=INDEX($A$3:$A$11,SMALL(IF(($C$3:$N$11="O")*($B$3:$B$11=$C$14)*($C$2:$N$2=$C$15),MATCH(ROW($A$3:$A$11),ROW($A$3:$A$11)),""),ROWS($A$1:A1)))

Array formula in cell C18:
=INDEX($A$3:$A$11,SMALL(IF(($C$3:$N$11="S")*($B$3:$B$11=$C$14)*($C$2:$N$2=$C$15),MATCH(ROW($A$3:$A$11),ROW($A$3:$A$11)),""),ROWS($A$1:A1)))

 

[MickG]

Try this example file for results on sheet 2, starting "A5", based on selection From "B1" Or "B2" on sheet2.
https://app.box.com/s/dcq1v10sl5lrs5u9wxrqn0zdq8anx5o4

 

[mooshoo]

Thank you both - I spent like 2 hours trying different things with no success lol