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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Cleaner[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]Building 1[/TD]
[TD]Adam[/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Building 2[/TD]
[TD]Brenda[/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Building 3[/TD]
[TD]Charlie[/TD]
[TD][/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]Building 4[/TD]
[TD]Denise[/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Building 5[/TD]
[TD]Edward[/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Building 6[/TD]
[TD]Brenda[/TD]
[TD][/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]Building 7[/TD]
[TD]Brenda[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]Building 8[/TD]
[TD]Denise[/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Building 9[/TD]
[TD]Adam[/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[TD][/TD]
[TD][/TD]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD]O[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Brenda[/TD]
[TD]<-- dropdown[/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]March[/TD]
[TD]<-- dropdown[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Full[/TD]
[TD]Outside[/TD]
[TD]Sweep Only[/TD]
[/TR]
[TR]
[TD]Building 6[/TD]
[TD]Building 7[/TD]
[TD]Building 9[/TD]
[/TR]
[TR]
[TD]Building 13[/TD]
[TD]Building 10[/TD]
[TD]Building 14[/TD]
[/TR]
[TR]
[TD]Building 16[/TD]
[TD]Building 12[/TD]
[TD]Building 25[/TD]
[/TR]
[TR]
[TD]Building 22[/TD]
[TD]Building 15[/TD]
[TD]Building 27[/TD]
[/TR]
[TR]
[TD]Building 23[/TD]
[TD]Building 17[/TD]
[TD]Building 28[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][th]

M​

[/th][th]

N​

[/th][th]

O​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Location[/td][td]Cleaner[/td][td]Jan[/td][td]Feb[/td][td]Mar[/td][td]Apr[/td][td]May[/td][td]Jun[/td][td]Jul[/td][td]Aug[/td][td]Sep[/td][td]Oct[/td][td]Nov[/td][td]Dec[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Building 1[/td][td]Adam[/td][td]F[/td][td][/td][td][/td][td]O[/td][td][/td][td][/td][td]S[/td][td][/td][td][/td][td]O[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Building 2[/td][td]Brenda[/td][td][/td][td]F[/td][td][/td][td][/td][td]O[/td][td][/td][td][/td][td]S[/td][td][/td][td][/td][td]O[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Building 3[/td][td]Charlie[/td][td][/td][td][/td][td]F[/td][td][/td][td][/td][td]O[/td][td][/td][td][/td][td]S[/td][td][/td][td][/td][td]O[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Building 4[/td][td]Denise[/td][td]F[/td][td][/td][td][/td][td]O[/td][td][/td][td][/td][td]S[/td][td][/td][td][/td][td]O[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]Building 5[/td][td]Edward[/td][td]O[/td][td][/td][td][/td][td]F[/td][td][/td][td][/td][td]O[/td][td][/td][td][/td][td]S[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Building 6[/td][td]Brenda[/td][td][/td][td][/td][td]F[/td][td][/td][td][/td][td]O[/td][td][/td][td][/td][td]S[/td][td][/td][td][/td][td]O[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]Building 7[/td][td]Brenda[/td][td][/td][td][/td][td]O[/td][td][/td][td][/td][td]F[/td][td][/td][td][/td][td]O[/td][td][/td][td][/td][td]S[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]Building 8[/td][td]Denise[/td][td]O[/td][td][/td][td][/td][td]F[/td][td][/td][td][/td][td]O[/td][td][/td][td][/td][td]S[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]Building 9[/td][td]Adam[/td][td][/td][td]F[/td][td][/td][td][/td][td]O[/td][td][/td][td][/td][td]S[/td][td][/td][td][/td][td]O[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]Employee[/td][td][/td][td]Brenda[/td][td][/td][td][/td][td]<-- dropdown[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]Month[/td][td][/td][td]Mar[/td][td][/td][td][/td][td]<-- dropdown[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]Full[/td][td]Outside[/td][td]Sweep Only[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]Building 6[/td][td]Building 7[/td][td]

#NUM!​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td]

#NUM!​

[/td][td]

#NUM!​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet17[/td][/tr][/table]

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