Counting Unique Records, Multiple Criteria

[databasedonr]

Hi,

I have an Excel Spreadsheet (2010) with 50K+ rows that has transaction information for individuals. I have DOB, unique ID, and transaction information in the columns. For each person, I may have dozens/hundreds of entries. What I would like to do is count how many persons I have using services based on their year of birth. I have created a list of unique records for birth year, so I have that range, and I need to count how many unique people from that year used services - not how many times an individual came in, but how many individuals, by birth year ... and I have no clue! I've tried a frequency array, but that gives me the number of visits by birth year, but not the number of unique individuals....

 

[Brianwarnock]

The attached is in 2002 but I think you can read it with 2010

there are 2 subs for sheet1 as I made a mistake regarding your requirement at first, plus I dont exactly know what your sheet looks like so this is really only a guide as to how it can be done.

Hope it helps

Brian

 

[databasedonr]

Thanks Brian; I was trying to do this with formulae. I'll dive into the code and, providing I can replicate it, I'm away. This looks like it will work - thanks ever so much.

 

[Brianwarnock]

NBVC is a formula wizard so if he comes on he might have a way, I don't.

If you have a problem post back, i suppose I should have annotated the code but wasn't sure it was ok for you.

Brian

 

[databasedonr]

Thanks again, Brian, but I am a bit thick; this works perfectly if I place my columns in positions 1 and 2. The only challenge I have understanding where you identify which column to look into for the year and the ID. If I get that, I'm off to the races.

Thanks again,

Don

 

[Brianwarnock]

.Cells(rownumber,columnnumber) is the format, actually I think you can put column letter in quotes
Eg cells(2,"A")
Normally when using Cells I'm indexing which is why I tend to automatically use numbers.

Brian

 

[databasedonr]

That's it, works a treat. Thanks again.

 

[Brianwarnock]

Thanks for the feedback. It's always nice tio know things have worked out.

Brian

 

[databasedonr]

Really, thanks again, as it was a tremendous help. It ran in about 1 second to solve a problem that otherwise would have taken me hours. If I'm ever in Merseyside, I'll buy you a pint, or if you're ever in Canada....