Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count unique dates within each unique ID
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count unique dates within each unique ID
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 he http://sulprobil.com/html/pfreq.html Just press ALT + F11 and copy the macro code into a new module. Regards, Bernd |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count unique dates within each unique ID
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$500 0<"")) Change the 5000s to some other number to adjust your range as necessary. "CD27" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count unique dates within each unique ID
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 -- Biff Microsoft Excel MVP "CD27" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count unique dates within each unique ID
SUMPRODUCT(($A$1:$A$5000=D2)/COUNTIF($B$1:$B$5000,$B$1:$B$5000&"")*($B$1:$B$500 0<""))
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 -- Biff Microsoft Excel MVP "~L" wrote in message ... 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$500 0<"")) Change the 5000s to some other number to adjust your range as necessary. "CD27" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count unique dates within each unique ID
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 "CD27" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count unique dates within each unique ID
Thanks everyone.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to count the number of unique dates | Excel Worksheet Functions | |||
Pivot Table Sub Total Count Unique Dates | Excel Worksheet Functions | |||
Attempting to sort unique/only count first record in each unique g | Excel Discussion (Misc queries) | |||
How can I count the number of unique dates in a Pivot Table? | Excel Discussion (Misc queries) | |||
Charting count of unique dates in a list | Charts and Charting in Excel |