Identify values with more than one date associated

[agriswold]

Hello,
I have a list of people who took an action and the date onwhich the action was performed. Some people have the same date listed formultiple actions, others have more than one date for multiple actions. I need toidentify (maybe in a new column so it can be sorted/counted in a pivot table)if a person took an action on more than one day. Any suggestions are greatlyappreciated!

 

[Norie]

If you only have 3 columns, people, date and action couldn't you create a table directly from that to show the no of times a person took an action?

 

[agriswold]

If you only have 3 columns, people, date and action couldn't you create a table directly from that to show the no of times a person took an action?

Thanks for your response! I only need to count the number of people who took an action on more than one day, not the total number of times they took an action.

 

[Norie]

Can you post some sample data?

 

[agriswold]

Can you post some sample data?

[TABLE="width: 304"]
<colgroup><col width="174" style="width: 131pt; mso-width-source: userset; mso-width-alt: 6363;"> <col width="230" style="width: 173pt; mso-width-source: userset; mso-width-alt: 8411;"> <tbody>[TR]
[TD="width: 174, bgcolor: transparent"]Action Date[/TD]
[TD="width: 230, bgcolor: transparent"]Person[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/11/2018[/TD]
[TD="bgcolor: transparent"]John Smith[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/11/2018[/TD]
[TD="bgcolor: transparent"]John Smith[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/11/2018[/TD]
[TD="bgcolor: transparent"]John Smith[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/11/2018[/TD]
[TD="bgcolor: transparent"]Sue Smith[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/11/2018[/TD]
[TD="bgcolor: transparent"]Sue Smith[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/12/2018[/TD]
[TD="bgcolor: transparent"]Sue Smith[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/12/2018[/TD]
[TD="bgcolor: transparent"]Sue Smith[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/12/2018[/TD]
[TD="bgcolor: transparent"]Sue Smith[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/12/2018[/TD]
[TD="bgcolor: transparent"]Sue Smith[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/12/2018[/TD]
[TD="bgcolor: transparent"]Sue Smith[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/12/2018[/TD]
[TD="bgcolor: transparent"]Sue Smith[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/12/2018[/TD]
[TD="bgcolor: transparent"]Joe Brown[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/12/2018[/TD]
[TD="bgcolor: transparent"]Joe Brown[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/12/2018[/TD]
[TD="bgcolor: transparent"]Joe Brown[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/12/2018[/TD]
[TD="bgcolor: transparent"]Jane Brown[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/12/2018[/TD]
[TD="bgcolor: transparent"]Jane Brown[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/12/2018[/TD]
[TD="bgcolor: transparent"]Jane Brown[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/13/2018[/TD]
[TD="bgcolor: transparent"]Jane Brown[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/13/2018[/TD]
[TD="bgcolor: transparent"]Jane Brown[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6/13/2018[/TD]
[TD="bgcolor: transparent"]Jane Brown[/TD]
[/TR]
</tbody>[/TABLE]
In this example I would want to count Sue Smith and Jane Brown because they made actions on more than one date.

 

[Marcelo Branco]

Maybe with a helper column


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Action Date​

[/td][td]

Person​

[/td][td]

Helper​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

11/06/2018​

[/td][td]

John Smith​

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

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

11/06/2018​

[/td][td]

John Smith​

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

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

11/06/2018​

[/td][td]

John Smith​

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

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

11/06/2018​

[/td][td]

Sue Smith​

[/td][td]

x​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

11/06/2018​

[/td][td]

Sue Smith​

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

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

12/06/2018​

[/td][td]

Sue Smith​

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

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

12/06/2018​

[/td][td]

Sue Smith​

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

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

12/06/2018​

[/td][td]

Sue Smith​

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

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

12/06/2018​

[/td][td]

Sue Smith​

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

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

12/06/2018​

[/td][td]

Sue Smith​

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

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

12/06/2018​

[/td][td]

Sue Smith​

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

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td]

12/06/2018​

[/td][td]

Joe Brown​

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

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

12/06/2018​

[/td][td]

Joe Brown​

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

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td]

12/06/2018​

[/td][td]

Joe Brown​

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

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td]

12/06/2018​

[/td][td]

Jane Brown​

[/td][td]

x​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

17​

[/td][td]

12/06/2018​

[/td][td]

Jane Brown​

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

[tr][td="bgcolor: #DCE6F1"]

18​

[/td][td]

12/06/2018​

[/td][td]

Jane Brown​

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

[tr][td="bgcolor: #DCE6F1"]

19​

[/td][td]

13/06/2018​

[/td][td]

Jane Brown​

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

[tr][td="bgcolor: #DCE6F1"]

20​

[/td][td]

13/06/2018​

[/td][td]

Jane Brown​

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

[tr][td="bgcolor: #DCE6F1"]

21​

[/td][td]

13/06/2018​

[/td][td]

Jane Brown​

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

Formula in C2 copied down
=IF(COUNTIF(B$2:B2,B2)=1,IF(COUNTIFS(B:B,B2,A:A,"<>"&A2),"x",""),"")

Hope this helps

M.