Lookup formula that omits duplicates

[Drew_]

Hi all.

I have a large data set where I am trying to determine which people worked on specific days. However, there are some instances where multiple people worked on the same day, so I am getting a double count; I only want to count it once. I have 1000 rows of data and I've provided some example data:

[TABLE="width: 365"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]John[/TD]
[TD]September[/TD]
[TD][/TD]
[TD]9/13/2017 [/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]September[/TD]
[TD][/TD]
[TD]9/13/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]September[/TD]
[TD][/TD]
[TD]9/14/2017[/TD]
[TD] 1[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 365"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Paul[/TD]
[TD]September[/TD]
[TD][/TD]
[TD]9/11/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]September[/TD]
[TD][/TD]
[TD]9/13/2017[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]September[/TD]
[TD][/TD]
[TD]9/13/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]September[/TD]
[TD][/TD]
[TD]9/13/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]September[/TD]
[TD][/TD]
[TD]9/15/2017[/TD]
[TD] 1[/TD]
[/TR]
</tbody>[/TABLE]


As you can see, a 1 appears each time a person starts a new workday; however, John and Paul both worked on the 13th so my formula is producing a 1 in both cells. I only want a 1 to appear the first time the new date appears (so for Paul on the 13th it would be a blank like the others).

To produce the 1, my formula is very simple:[TABLE="width: 59"]
<tbody>[TR]
[TD="class: xl65, width: 59"][TABLE="width: 59"]
<tbody>[TR]
[TD="class: xl65, width: 59"]
=IF(D473=D472,"",1)

where D has the dates


[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
I am thinking I need some sort of combination of ROW and OFFSET. Anyone have any ideas? Please let me know if I was not clear enough, thanks for any help

 

[lrobbo314]

Assuming your data is in A1:C8, you could put this in D1 and copy down.

=IF(COUNTIF($D$1:D1,D1)=1,1,"")

Otherwise, if you are just looking for a count of total unique dates, then you could use this formula.

=SUMPRODUCT(1/COUNTIF(D1:D8,D1:D8))

Adjust the ranges to fit your actual data.

 

[Drew_]

This works! Thank you so much!

 

[lrobbo314]