Count unique dates within each unique ID

[CD27]

I have 2 columns one with a person ID and the other with various
dates. The file is sorted by person ID. I need to count each unique
date within each ID. So for example if ID 1234 has 3 dates in column
b 8/14, 8/15 and 8/15 again, it would only count 2 and not 3 at the
last occurrence in cell A which is the ID.

100310 5/6/09
136438 5/15/09
136438 5/16/09
136438 5/16/09
279700 5/21/09
296296 5/1/09
316073 5/26/09
329053 5/12/09


Thanks for any help you can provide.

CD

 

[Bernd P]

Hello,

I suggest not to solve this with worksheet functions but to use my UDF
Pfreq:
array-enter into a sufficiently long range with 2 columns:
=Pfreq(Pfreq(A1:A999,B1:B999))

Pfreq you can find here:
http://sulprobil.com/html/pfreq.html

Just press ALT + F11 and copy the macro code into a new module.

Regards,
Bernd

 

[~L]

Use advanced filter to create a list of unique values from column A in column
D, with the data starting in row 2, then in E2 enter:

=SUMPRODUCT(($A$1:$A$5000=D2)/COUNTIF($B$1:$B$5000,$B$1:$B$5000&"")*($B$1:$B$5000<>""))

Change the 5000s to some other number to adjust your range as necessary.

 

[T. Valko]

Since dates are really just numbers formatted to display as dates you want
to count the unique numbers (dates) that meet a condition.

See this:

http://tinyurl.com/m238jd

 

[T. Valko]

SUMPRODUCT(($A$1:$A$5000=D2)/COUNTIF($B$1:$B$5000,$B$1:$B$5000&"")*($B$1:$B$5000<>""))

That formula will return an incorrect result if multiple ID's have the same
dates.

...........A..........B..........D
1........x......1/1/09
2........x......1/2/09.......X
3........x......1/3/09
4........y......1/3/09
5........y......1/7/09

 

[Ashish Mathur]

Hi,

How are you? You could try this non-formula approach. Give a heading to
the 2 columns and then select the entire range including the headers to
assign it a name, say Range1. Now convert it to a list (Ctrl+L). Now save
the file

Click on any blank cell and go to Data > Import External data > New Database
query. Select Excel files and click on Next. On the last screen, select
the radio button for Edit query in MS Query. Cluck on Finish. Now go to
Edit > Properties and check the box for unique records. Now go to file and
select the last option - Return data to MS Excel. Now click on the
properties button and select rhe box for refresh every 1 minutes.

Clcik on Finish. You will now have all the unique entries. Now that you
have the unique records, you may create a pivot table with IS in the row
area and dates in the data area - you will get the count of unique dates per
ID

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[CD]

Thanks everyone.