Counting Unique Values

  • Thread starter Thread starter Stats
  • Start date Start date
S

Stats

I am sure there are numerous posts against my query however, having
spent most of the evening on this I cannot find one suitable for my
problem.

I have a table that has 2 columns:
1 - Account numbers
2 - Dates

The Account Numbers may appear more than once with different dates
against them. However, the list includes more than 1 Account Numbers
for the same date.

I want to use a formula (& not the Unique Value Filter), array if need
be, to count the number of unique Account Numbers (i.e. only count the
account number for as ingle date, even where duplciates exist).

Any help would be much appreciated.
 
I am sure there are numerous posts against my query however, having
spent most of the evening on this I cannot find one suitable for my
problem.

I have a table that has 2 columns:
1 - Account numbers
2 - Dates

The Account Numbers may appear more than once with different dates
against them.  However, the list includes more than 1 Account Numbers
for the same date.

I want to use a formula (& not the Unique Value Filter), array if need
be, to count the number of unique Account Numbers (i.e. only count the
account number for as ingle date, even where duplciates exist).

Any help would be much appreciated.
Click to expand...

I should also have included that I have created 2 additional columns
that are the unique values for Columns 1 & 2, that I am seeking to use
as the conditions that the array formula should use to match against
and count for only unique values.
Many thanks
 
Can you post a small sample of your data and tell us what result you expect?

--
Biff
Microsoft Excel MVP


I am sure there are numerous posts against my query however, having
spent most of the evening on this I cannot find one suitable for my
problem.

I have a table that has 2 columns:
1 - Account numbers
2 - Dates

The Account Numbers may appear more than once with different dates
against them. However, the list includes more than 1 Account Numbers
for the same date.

I want to use a formula (& not the Unique Value Filter), array if need
be, to count the number of unique Account Numbers (i.e. only count the
account number for as ingle date, even where duplciates exist).

Any help would be much appreciated.
Click to expand...

I should also have included that I have created 2 additional columns
that are the unique values for Columns 1 & 2, that I am seeking to use
as the conditions that the array formula should use to match against
and count for only unique values.
Many thanks
 
I am sure there are numerous posts against my query however, having
spent most of the evening on this I cannot find one suitable for my
problem.

I have a table that has 2 columns:
1 - Account numbers
2 - Dates

The Account Numbers may appear more than once with different dates
against them.  However, the list includes more than 1 Account Numbers
for the same date.

I want to use a formula (& not the Unique Value Filter), array if need
be, to count the number of unique Account Numbers (i.e. only count the
account number for as ingle date, even where duplciates exist).

Any help would be much appreciated.
Click to expand...
 
- Show quoted text -
Click to expand...

Example data:

ID Date
62114167 30/11/2009 00:00:00
62114167 30/11/2009 00:00:00
62114167 02/12/2009 00:00:00
62114171 16/12/2009 00:00:00
62114171 16/12/2009 00:00:00
62114171 16/12/2009 00:00:00
62114182 03/02/2010 00:00:00
62114192 29/11/2009 00:00:00
62114192 29/11/2009 00:00:00
62114192 30/11/2009 00:00:00
62114192 30/11/2009 00:00:00
62114192 30/11/2009 00:00:00
62114192 30/11/2009 00:00:00

I then have 2 additional columns that list out all the unique IDs &
Dates.
I want to create an additional column that counts unique entries based
on ID & Date criteria (i.e. if ID = 62114167 and date = 30/11/2009
count once and disregard other entries matching this criteria).

Many thanks
 
Ok, it's still not clear!

It looks like the dates are actually dates plus a time in the same cell.

So, do you want to count the unique *times* on a particular date for a
particular ID?

--
Biff
Microsoft Excel MVP


- Show quoted text -
Click to expand...

Example data:

ID Date
62114167 30/11/2009 00:00:00
62114167 30/11/2009 00:00:00
62114167 02/12/2009 00:00:00
62114171 16/12/2009 00:00:00
62114171 16/12/2009 00:00:00
62114171 16/12/2009 00:00:00
62114182 03/02/2010 00:00:00
62114192 29/11/2009 00:00:00
62114192 29/11/2009 00:00:00
62114192 30/11/2009 00:00:00
62114192 30/11/2009 00:00:00
62114192 30/11/2009 00:00:00
62114192 30/11/2009 00:00:00

I then have 2 additional columns that list out all the unique IDs &
Dates.
I want to create an additional column that counts unique entries based
on ID & Date criteria (i.e. if ID = 62114167 and date = 30/11/2009
count once and disregard other entries matching this criteria).

Many thanks
 
I'm assuming that the dates are important and the time is just a formatting
problem.

For the second time in a week I'm suggesting that John Walkenbach's UDF is
what you want. You can download this from:

www.spreadsheetpage.com/index.php/site/tip/identifying_unique_values_in_an_array_or_range/

I gave your sample data a range name for Ids and Dates. You enter the
formula twice, Once to find the total number of unique items then again to
list the items.

To get the number enter =uniqueitems(Ids & Dates) and commit with Crtl +
Shift + Enter (CSE).

Now select this number of rows and write the formula:
=TRANSPOSE(uniqueitems(Ids&Dates,FALSE))

and commit with CSE.

to get the total of each unique item use the formula:
=SUMPRODUCT(--(Ids&Dates=E9)) where E9 is the first unique item from your
formula. Copy this down.

If you have not used a macro before follow these steps.

Press ALT + F11, choose Insert, Module now paste the function from JW's
site. Save the workbook and press ALT + Q to return to the workbook.

You can now enter the formulas.

If this helps please hit 'Yes'

Peter Atherton
 
Back
Top