Returning a Single List from Data with Multiple Occurrences

O

Orion19

Board Regular
Joined
Dec 18, 2017
Messages
56
Hello!

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]1/4/18
[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]1/4/18[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]1/4/18[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]1/5/18[/TD]
[TD]Fred[/TD]
[/TR]
[TR]
[TD]1/6/18[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]1/6/18[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]1/7/18[/TD]
[TD]Fred[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Mary[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]50%[/TD]
[/TR]
</tbody>[/TABLE]

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!!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
try like this:


Excel 2010
ABCDE
1DateNameNameDays Worked3
21/4/2018MaryMary0.5
31/4/2018MaryFred0.5
41/4/2018MaryJohnny0.25
51/5/2018Fred
61/6/2018Mary
71/6/2018Johnny
81/7/2018Fred
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
 
Upvote 0
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))}

VBA Geek said:
try like this:

Excel 2010
ABCDE
Mary
Mary
Mary
Fred
Mary
Johnny
Fred

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: center"]Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: center"]Days Worked[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/4/2018[/TD]

[TD="align: center"]Mary[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/4/2018[/TD]

[TD="align: center"]Fred[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1/4/2018[/TD]

[TD="align: center"]Johnny[/TD]
[TD="align: center"]0.25[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1/5/2018[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1/6/2018[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1/6/2018[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1/7/2018[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet4​



Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]{=SUM(1/COUNTIFS(B2:B8,B2:B8))}[/TD]
[/TR]
</tbody>[/TABLE]
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[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]{=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)))))}[/TD]
[/TR]
</tbody>[/TABLE]
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[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=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)))}[/TD]
[/TR]
</tbody>[/TABLE]
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
Click to expand...
 
Upvote 0
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!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

tezza
  • Solved
Sumif Subtotal help or better option
Replies
3
Views
182
Fluff
Fluff
L
  • Question Question
Macro to correct entries across multiple sheets
Replies
1
Views
187
Lux Aeterna
L
W
  • Question Question
Calculate total time taken to complete task in working days (6am to 6pm)
Replies
4
Views
458
Wibab
W
A
  • Question Question
How restrict copy data from form to sheet based on five days within current month
Replies
3
Views
78
Ali M
A
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
329
excelnewbie999
E

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top