Counting Unique Occurrences in a Database

[susieqhersh]

This is a subset of a database that contains many project sponsors. I need to count the number of unique Project Leader Names for a sponsor when the Include in Projects Completed is "Y". The sponsor name will be provided through an input form.

[Maistrye]

This is a subset of a database that contains many project sponsors. I need to count the number of unique Project Leader Names for a sponsor when the Include in Projects Completed is "Y". The sponsor name will be provided through an input form.

If you can load it into Access, the following Query should give you that information. (MyTable being whatever name you assign to your table with the fields ProjectSponsor, ProjectLeader, IncludeInProjectsCompleted)

Please Login or Register  to view this content.

To do this in Excel, I'd use an Advanced filter to obtain a unique set of records that have "Y" in the "Include In Projects Completed" column and then do a count based on that. (Data -> Filter -> Advanced Filter) (Copy the relevant columns to a separate sheet, and play with it there.)

Scott

Scott

[susieqhersh]

I need an Excel solution and I have too many sponsor names for the advanced filter approach to be workable but thanks for you input.

[MDubbelboer]

I need an Excel solution and I have too many sponsor names for the advanced filter approach to be workable but thanks for you input.

i don't think the advanced filter has a limit on it like the autofilter...are you perhaps thinking of the autofilter command? the advanced filter is found in the same menu.

if you can sort by include in projects completed, and manually kill all the non-Y values and then do an advanced filter with unique records you should be set

[Maistrye]

I need an Excel solution and I have too many sponsor names for the advanced filter approach to be workable but thanks for you input.

You mentioned the Autofilter. The Autofilter and the Advanced Filter are two different beasts.

If you meant to say the Advanced Filter won't do it for you, you can ignore this.

Attached is a spreadsheet with a sample of what you can do with the Advanced Filter. You'll have to adjust the parameters depending on the number of lines (obviously). I will admit that it is not "automatic". But it should work assuming you have less than 65,000 lines.

Scott