Counting Unique ID's

[Agent86]

Hi,

I'm wrestling with how to approach this.

I've got a list that's about 15,000 rows long. Column A has a date and column B has an alpha numeric ID. The list is sorted in order of oldest date at the top, most recent at the bottom. The range of dates is from 1/1/2007 through 3/23/2009. Both the same date and ID show up multiple time throughout the list.

I need to figure out how many unique ID's show up on the list in each given calendar month. I could manually break up the list into months, use an advanced filter and then run a count formula, but it seems like there must be an easier approach.

Anyone have any ideas?

 

[c_m]

you need to right click on the field and you will find that option.
Here is a demo showing that:
http://www.datapigtechnologies.com/flashfiles/pivot5.html
I think the procedure is also applicable for 2007 version (not sure though)

 

[Agent86]

Thanks for the link, c_m. And thanks to both you and Gerald for all the kind assistance. Problem solved!

Pivot tables are cool. Now I'm excited to try other things with them as well.

 

[barry houdini]

Here's a possible formula approach.......

List first of each month in D2 down, e.g. in D2 1/1/2007, in D3, 2/1/2007 in D4 3/1/2007 etc.

Now in E2 use this formula

=IF(SUM(--(TEXT(A$2:A$15000,"mmmyy")=TEXT(D2,"mmmyy"))),SUM(IF(FREQUENCY(MATCH(OFFSET(B$2,MATCH(TEXT(D2,"mmmyy"),TEXT(A$2:A$15000,"mmmyy"),0)-1,0,SUM(--(TEXT(A$2:A$15000,"mmmyy")=TEXT(D2,"mmmyy")))),OFFSET(B$2,MATCH(TEXT(D2,"mmmyy"),TEXT(A$2:A$15000,"mmmyy"),0)-1,0,SUM(--(TEXT(A$2:A$15000,"mmmyy")=TEXT(D2,"mmmyy")))),0),ROW(A$2:A$15000)-ROW(A$2)+1),1)),0)

confirmed with CTRL+SHIFT+ENTER and copied down......

 

[Domenic]

Here's another possible formula approach, which also takes advantage of the fact that the data is already sorted by date, in ascending order...

Assumptions

Column A (A2 onward) contains the date

Column B (B2 onward) contains the ID

Data is sorted by date, in ascending order

D2 contains 1/1/2007 (the first of the month of January for 2007)

D3 contains 2/1/2007 (the first of the month of February for 2007)

...and so on.

Defined Names

Select cell E2

Insert > Name > Define

Name:  Date

Refers to:

=INDEX(Sheet1!$A$2:$A$65536,MATCH(Sheet1!$D2,Sheet1!$A$2:$A$65536,0)):INDEX(Sheet1!$A$2:$A$65536,MATCH(Sheet1!$D2,Sheet1!$A$2:$A$65536,1))

Click Add

Name:  ID

Refers to:

=INDEX(Sheet1!$B$2:$B$65536,MATCH(Sheet1!$D2,Sheet1!$A$2:$A$65536,0)):INDEX(Sheet1!$B$2:$B$65536,MATCH(Sheet1!$D2,Sheet1!$A$2:$A$65536,1))

Change the sheet references accordingly

Click Ok

Formula

E2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(FREQUENCY(IF(Date-DAY(Date)+1=D2,IF(ID<>"",MATCH("~"&ID,ID&"",0))),ROW(Date)-MIN(ROW(Date))+1),1))

Hope this helps!

xl-central.com

 

[Trebor76]

Hi Agent86,

The following array formula (confirmed by CTRL+SHIFT+ENTER) also worked for me:

{=IF(MONTH(A2)=MONTH(A3),"",IF(SUM(INDIRECT("C2:C"&ROW()-1))=0,SUM(IF(FREQUENCY(INDIRECT("B2:B"&ROW()),INDIRECT("B2:B"&ROW()))>0,1,0)),SUM(IF(FREQUENCY(INDIRECT("B2:B"&ROW()),INDIRECT("B2:B"&ROW()))>0,1,0))-SUM(INDIRECT("C2:C"&ROW()-1))))}

HTH

Robert

 

[Agent86]

You guys are all too much! You know I'm going to stay up half the night now trying all the different options.

... I've got so much to learn.

Thanks again!