Returning a Single List from Data with Multiple Occurrences

[Orion19]

Hello!

I'm trying to bend Excel to my will but it refuses. Here's an example of my data set:

Date	Name
1/4/18	Mary
1/4/18	Mary
1/4/18	Mary
1/5/18	Fred
1/6/18	Mary
1/6/18	Mary
1/7/18	Fred

<tbody>
</tbody>

Each date is a day someone worked. Each name is the name of the person that worked that day. I want to generate a list that shows all the people that worked (listed once) along with the percentage of days they worked. For example, output based on the list above would be:

Mary	50%
Fred	50%

<tbody>
</tbody>

There were four days total in the raw data. Even though Mary is listed five times in the raw data she worked 50% of the days. Fred is only listed twice in the raw data but he worked the other 50% of days. I assume I'd need an array formula but I'm stuck on how to approach it. Any help is appreciated!!

 

[VBA Geek]

try like this:

Excel 2010
	A	B	C	D	E
1	Date	Name	Name	Days Worked	3
2	1/4/2018	Mary	Mary	0.5
3	1/4/2018	Mary	Fred	0.5
4	1/4/2018	Mary	Johnny	0.25
5	1/5/2018	Fred
6	1/6/2018	Mary
7	1/6/2018	Johnny
8	1/7/2018	Fred
9
10
11
12
Sheet4

<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E1</th><td style="text-align:left">{=SUM(<font color="Blue">1/COUNTIFS(<font color="Red">B2:B8,B2:B8</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.</td></tr></table><br />


<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">C$2:C2</font>)>$E$1,"",INDEX(<font color="Red">$B$2:$B$8,SMALL(<font color="Green">MATCH(<font color="Purple">$B$2:$B$8,$B$2:$B$8,0</font>),1+SUM(<font color="Purple">COUNTIFS(<font color="Teal">$B$2:$B$8,C$1:C1</font>)</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.</td></tr></table><br /> and drag down

<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">{=IF(<font color="Blue">C2="","",SUM(<font color="Red">(<font color="Green">IF(<font color="Purple">$B$2:$B$8=C2,1/COUNTIFS(<font color="Teal">$A$2:$A$8,$A$2:$A$8,$B$2:$B$8,C2</font>)</font>)</font>)</font>)/(<font color="Red">1+MAX(<font color="Green">$A$2:$A$8</font>)-MIN(<font color="Green">$A$2:$A$8</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.</td></tr></table><br /> and drag down

 

[Orion19]

Thank you so much for the reply! I'm heavily invested in this project and am grateful for the help!

I gave that a shot but got #DIV/0 so it looks like the COUNTIFS portion isn't functioning for me. If I enter {=COUNTIFS(RawData!D2:D15000, RawData!D2:D15000)} it returns 424 (regardless of whether its normal or as an array). The actual count should be 13 (there are 13 different names in my range RawData!D2:D15000). I entered the first formula as an array but still got #DIV/0 as a result. Any ideas what I'm doing wrong? Here is the full formula as I entered it:

{=SUM(1/COUNTIFS(RawData!D2:D15000, RawData!D2:D15000))}

try like this:

Excel 2010

	A	B	C	D	E
1	Date	Name	Name	Days Worked	3
2	1/4/2018	Mary	Mary	0.5
3	1/4/2018	Mary	Fred	0.5
4	1/4/2018	Mary	Johnny	0.25
5	1/5/2018	Fred
6	1/6/2018	Mary
7	1/6/2018	Johnny
8	1/7/2018	Fred
9
10
11
12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Array Formulas

Cell	Formula
E1	{=SUM(1/COUNTIFS(B2:B8,B2:B8))}

<thead>
</thead><tbody>
</tbody>

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself



Array Formulas

Cell	Formula
C2	{=IF(ROWS(C$2:C2)>$E$1,"",INDEX($B$2:$B$8,SMALL(MATCH($B$2:$B$8,$B$2:$B$8,0),1+SUM(COUNTIFS($B$2:$B$8,C$1:C1)))))}

<thead>
</thead><tbody>
</tbody>

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
and drag down

Array Formulas

Cell	Formula
D2	{=IF(C2="","",SUM((IF($B$2:$B$8=C2,1/COUNTIFS($A$2:$A$8,$A$2:$A$8,$B$2:$B$8,C2))))/(1+MAX($A$2:$A$8)-MIN($A$2:$A$8)))}

<thead>
</thead><tbody>
</tbody>

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
and drag down

 

[Orion19]

I got it! My range included blank cells and I needed to compensate for that. Thanks so much for your help!!! Here's what worked:

{=SUM(1/COUNTIFS(RawData!D2:D15000, RawData!D2:D15000 & ""))-1}

The count included the header so that's where the "-1" comes from. Thanks again!!!