Count unique entries

One try ..

Assume the posted data is in Sheet1,
cols A to E, data from row2 down

Using 3 empty cols to the right, say cols G to I, put:

In G2: =B2&"_"&D2
In H2: =IF(COUNTIF($G$2:G2,G2)>1,"",G2)
In I2: =IF(H2="","",B2)

Select G2:I2, fill down until the last row of data

In another sheet
-----------
The B codes are listed in A1 down, viz.:

ABCABC
ABCDDD
etc

Put in B1: =COUNTIF(Sheet1!I:I,A1)
Copy down

Col B will return the desired counts for the codes in col A
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"BeSmart" <[email protected]> wrote in message
news:[email protected]...
> I have a list of 900 entries and for each change of code in column B I
need
> to know the number of publications used in D excluding the duplication, ie
> for ABCABC there are 3 publications, for ABCDDD there are 4.
>
> A B C D
> ABC ABCABC A MELAGE
> ABC ABCABC A MELAGE
> ABC ABCABC A MELHER
> ABC ABCABC A DOGHAN
> ABC ABCDDD A MELAGE
> ABC ABCDDD A MELAGE
> ABC ABCDDD A SYDMOR
> ABC ABCDDD A MELHER
> ABC ABCDDD A BRICOU
>
> In a separate area I will then have a list of B codes with the formula
next
> to it that calculates the number of unique publications eg:
>
> ABCABC 3
> ABCDDD 4
>
> Any help with the formula I should use would be greatly appreciated.
> --
> Thank for your help
> BeSmart

Typo, sorry.

Line:
> cols A to E, data from row2 down

should read:
> cols A to D, data from row2 down
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

On Mon, 29 Aug 2005 17:33:16 -0700, "BeSmart"
<[email protected]> wrote:

>I have a list of 900 entries and for each change of code in column B I need
>to know the number of publications used in D excluding the duplication, ie
>for ABCABC there are 3 publications, for ABCDDD there are 4.
>
>A B C D
>ABC ABCABC A MELAGE
>ABC ABCABC A MELAGE
>ABC ABCABC A MELHER
>ABC ABCABC A DOGHAN
>ABC ABCDDD A MELAGE
>ABC ABCDDD A MELAGE
>ABC ABCDDD A SYDMOR
>ABC ABCDDD A MELHER
>ABC ABCDDD A BRICOU
>
>In a separate area I will then have a list of B codes with the formula next
>to it that calculates the number of unique publications eg:
>
>ABCABC 3
>ABCDDD 4
>
>Any help with the formula I should use would be greatly appreciated.

Here's one way.

1. Download and install Longre's morefunc.xll from http://xcell05.free.fr/

2. With the B code in H1, use this **ARRAY** formula:

=COUNTDIFF(IF(Code=H1,Publications),,FALSE)

To enter an **ARRAY** formula, after typing or pasting in the formula, hold
down <ctrl><shift> while you hit <enter>. Excel will place braces around the
formula.




--ron

Thanks for that
I've downloaded the morefunc.xll and included your formulas (as an array),
however it isn't taking into account the two conditions.

The result I got only told me how many times ABCABC occurs in the list.

What I need to know is:

"For everything in column B that says ABCABC, count and report the number of
different publications listed in column D.

Do I need to define name the column D list and call it "publications"?

--
Thank for your help
BeSmart


"Ron Rosenfeld" wrote:

> On Mon, 29 Aug 2005 17:33:16 -0700, "BeSmart"
> <[email protected]> wrote:
>
> >I have a list of 900 entries and for each change of code in column B I need
> >to know the number of publications used in D excluding the duplication, ie
> >for ABCABC there are 3 publications, for ABCDDD there are 4.
> >
> >A B C D
> >ABC ABCABC A MELAGE
> >ABC ABCABC A MELAGE
> >ABC ABCABC A MELHER
> >ABC ABCABC A DOGHAN
> >ABC ABCDDD A MELAGE
> >ABC ABCDDD A MELAGE
> >ABC ABCDDD A SYDMOR
> >ABC ABCDDD A MELHER
> >ABC ABCDDD A BRICOU
> >
> >In a separate area I will then have a list of B codes with the formula next
> >to it that calculates the number of unique publications eg:
> >
> >ABCABC 3
> >ABCDDD 4
> >
> >Any help with the formula I should use would be greatly appreciated.
>
> Here's one way.
>
> 1. Download and install Longre's morefunc.xll from http://xcell05.free.fr/
>
> 2. With the B code in H1, use this **ARRAY** formula:
>
> =COUNTDIFF(IF(Code=H1,Publications),,FALSE)
>
> To enter an **ARRAY** formula, after typing or pasting in the formula, hold
> down <ctrl><shift> while you hit <enter>. Excel will place braces around the
> formula.
>
>
>
>
> --ron
>

Here's another way...

Assuming that F1:F2 contains ABCABC and ABCDDD...

G1, copied down:

=SUM(IF(FREQUENCY(IF($B$1:$B$9=F1,MATCH($D$1:$D$9,$D$1:$D$9,0)),ROW($D$1:
$D$9)-ROW($D$1)+1)>0,1))

or

=COUNT(1/FREQUENCY(IF($B$1:$B$9=F1,MATCH($D$1:$D$9,$D$1:$D$9,0)),ROW($D$1
:$D$9)-ROW($D$1)+1))

Both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER.

Hope this helps!

In article <[email protected]>,
"BeSmart" <[email protected]> wrote:

> I have a list of 900 entries and for each change of code in column B I need
> to know the number of publications used in D excluding the duplication, ie
> for ABCABC there are 3 publications, for ABCDDD there are 4.
>
> A B C D
> ABC ABCABC A MELAGE
> ABC ABCABC A MELAGE
> ABC ABCABC A MELHER
> ABC ABCABC A DOGHAN
> ABC ABCDDD A MELAGE
> ABC ABCDDD A MELAGE
> ABC ABCDDD A SYDMOR
> ABC ABCDDD A MELHER
> ABC ABCDDD A BRICOU
>
> In a separate area I will then have a list of B codes with the formula next
> to it that calculates the number of unique publications eg:
>
> ABCABC 3
> ABCDDD 4
>
> Any help with the formula I should use would be greatly appreciated.

That works wonderfully - thanks heaps

"Domenic" wrote:

> Here's another way...
>
> Assuming that F1:F2 contains ABCABC and ABCDDD...
>
> G1, copied down:
>
> =SUM(IF(FREQUENCY(IF($B$1:$B$9=F1,MATCH($D$1:$D$9,$D$1:$D$9,0)),ROW($D$1:
> $D$9)-ROW($D$1)+1)>0,1))
>
> or
>
> =COUNT(1/FREQUENCY(IF($B$1:$B$9=F1,MATCH($D$1:$D$9,$D$1:$D$9,0)),ROW($D$1
> :$D$9)-ROW($D$1)+1))
>
> Both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just
> ENTER.
>
> Hope this helps!
>
> In article <[email protected]>,
> "BeSmart" <[email protected]> wrote:
>
> > I have a list of 900 entries and for each change of code in column B I need
> > to know the number of publications used in D excluding the duplication, ie
> > for ABCABC there are 3 publications, for ABCDDD there are 4.
> >
> > A B C D
> > ABC ABCABC A MELAGE
> > ABC ABCABC A MELAGE
> > ABC ABCABC A MELHER
> > ABC ABCABC A DOGHAN
> > ABC ABCDDD A MELAGE
> > ABC ABCDDD A MELAGE
> > ABC ABCDDD A SYDMOR
> > ABC ABCDDD A MELHER
> > ABC ABCDDD A BRICOU
> >
> > In a separate area I will then have a list of B codes with the formula next
> > to it that calculates the number of unique publications eg:
> >
> > ABCABC 3
> > ABCDDD 4
> >
> > Any help with the formula I should use would be greatly appreciated.
>

On Mon, 29 Aug 2005 20:33:10 -0700, "BeSmart"
<[email protected]> wrote:

>The result I got only told me how many times ABCABC occurs in the list.
>
>What I need to know is:
>
>"For everything in column B that says ABCABC, count and report the number of
>different publications listed in column D.
>
>Do I need to define name the column D list and call it "publications"?

I'm not sure what you are doing differently than I.

You can either NAME the appropriate ranges (Code=$B$2:$B$n
Publications=$D$2:$D$n) or use the cell references in their place in the
formula.

So if your table were in A2:D10, the formula could read:

=COUNTDIFF(IF($B$2:$B$10=H1,$D$2:$D$10),,FALSE)

again -- entered as an ARRAY formula.


--ron

I have a list of 900 entries and for each change of code in column B I need
to know the number of publications used in D excluding the duplication, ie
for ABCABC there are 3 publications, for ABCDDD there are 4.

A B C D
ABC ABCABC A MELAGE
ABC ABCABC A MELAGE
ABC ABCABC A MELHER
ABC ABCABC A DOGHAN
ABC ABCDDD A MELAGE
ABC ABCDDD A MELAGE
ABC ABCDDD A SYDMOR
ABC ABCDDD A MELHER
ABC ABCDDD A BRICOU

In a separate area I will then have a list of B codes with the formula next
to it that calculates the number of unique publications eg:

ABCABC 3
ABCDDD 4

Any help with the formula I should use would be greatly appreciated.
--
Thank for your help
BeSmart

One try ..

Assume the posted data is in Sheet1,
cols A to E, data from row2 down

Using 3 empty cols to the right, say cols G to I, put:

In G2: =B2&"_"&D2
In H2: =IF(COUNTIF($G$2:G2,G2)>1,"",G2)
In I2: =IF(H2="","",B2)

Select G2:I2, fill down until the last row of data

In another sheet
-----------
The B codes are listed in A1 down, viz.:

ABCABC
ABCDDD
etc

Put in B1: =COUNTIF(Sheet1!I:I,A1)
Copy down

Col B will return the desired counts for the codes in col A
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"BeSmart" <[email protected]> wrote in message
news:[email protected]...
> I have a list of 900 entries and for each change of code in column B I
need
> to know the number of publications used in D excluding the duplication, ie
> for ABCABC there are 3 publications, for ABCDDD there are 4.
>
> A B C D
> ABC ABCABC A MELAGE
> ABC ABCABC A MELAGE
> ABC ABCABC A MELHER
> ABC ABCABC A DOGHAN
> ABC ABCDDD A MELAGE
> ABC ABCDDD A MELAGE
> ABC ABCDDD A SYDMOR
> ABC ABCDDD A MELHER
> ABC ABCDDD A BRICOU
>
> In a separate area I will then have a list of B codes with the formula
next
> to it that calculates the number of unique publications eg:
>
> ABCABC 3
> ABCDDD 4
>
> Any help with the formula I should use would be greatly appreciated.
> --
> Thank for your help
> BeSmart

Typo, sorry.

Line:
> cols A to E, data from row2 down

should read:
> cols A to D, data from row2 down
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

On Mon, 29 Aug 2005 17:33:16 -0700, "BeSmart"
<[email protected]> wrote:

>I have a list of 900 entries and for each change of code in column B I need
>to know the number of publications used in D excluding the duplication, ie
>for ABCABC there are 3 publications, for ABCDDD there are 4.
>
>A B C D
>ABC ABCABC A MELAGE
>ABC ABCABC A MELAGE
>ABC ABCABC A MELHER
>ABC ABCABC A DOGHAN
>ABC ABCDDD A MELAGE
>ABC ABCDDD A MELAGE
>ABC ABCDDD A SYDMOR
>ABC ABCDDD A MELHER
>ABC ABCDDD A BRICOU
>
>In a separate area I will then have a list of B codes with the formula next
>to it that calculates the number of unique publications eg:
>
>ABCABC 3
>ABCDDD 4
>
>Any help with the formula I should use would be greatly appreciated.

Here's one way.

1. Download and install Longre's morefunc.xll from http://xcell05.free.fr/

2. With the B code in H1, use this **ARRAY** formula:

=COUNTDIFF(IF(Code=H1,Publications),,FALSE)

To enter an **ARRAY** formula, after typing or pasting in the formula, hold
down <ctrl><shift> while you hit <enter>. Excel will place braces around the
formula.




--ron

Thanks for that
I've downloaded the morefunc.xll and included your formulas (as an array),
however it isn't taking into account the two conditions.

The result I got only told me how many times ABCABC occurs in the list.

What I need to know is:

"For everything in column B that says ABCABC, count and report the number of
different publications listed in column D.

Do I need to define name the column D list and call it "publications"?

--
Thank for your help
BeSmart


"Ron Rosenfeld" wrote:

> On Mon, 29 Aug 2005 17:33:16 -0700, "BeSmart"
> <[email protected]> wrote:
>
> >I have a list of 900 entries and for each change of code in column B I need
> >to know the number of publications used in D excluding the duplication, ie
> >for ABCABC there are 3 publications, for ABCDDD there are 4.
> >
> >A B C D
> >ABC ABCABC A MELAGE
> >ABC ABCABC A MELAGE
> >ABC ABCABC A MELHER
> >ABC ABCABC A DOGHAN
> >ABC ABCDDD A MELAGE
> >ABC ABCDDD A MELAGE
> >ABC ABCDDD A SYDMOR
> >ABC ABCDDD A MELHER
> >ABC ABCDDD A BRICOU
> >
> >In a separate area I will then have a list of B codes with the formula next
> >to it that calculates the number of unique publications eg:
> >
> >ABCABC 3
> >ABCDDD 4
> >
> >Any help with the formula I should use would be greatly appreciated.
>
> Here's one way.
>
> 1. Download and install Longre's morefunc.xll from http://xcell05.free.fr/
>
> 2. With the B code in H1, use this **ARRAY** formula:
>
> =COUNTDIFF(IF(Code=H1,Publications),,FALSE)
>
> To enter an **ARRAY** formula, after typing or pasting in the formula, hold
> down <ctrl><shift> while you hit <enter>. Excel will place braces around the
> formula.
>
>
>
>
> --ron
>

Here's another way...

Assuming that F1:F2 contains ABCABC and ABCDDD...

G1, copied down:

=SUM(IF(FREQUENCY(IF($B$1:$B$9=F1,MATCH($D$1:$D$9,$D$1:$D$9,0)),ROW($D$1:
$D$9)-ROW($D$1)+1)>0,1))

or

=COUNT(1/FREQUENCY(IF($B$1:$B$9=F1,MATCH($D$1:$D$9,$D$1:$D$9,0)),ROW($D$1
:$D$9)-ROW($D$1)+1))

Both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER.

Hope this helps!

In article <[email protected]>,
"BeSmart" <[email protected]> wrote:

> I have a list of 900 entries and for each change of code in column B I need
> to know the number of publications used in D excluding the duplication, ie
> for ABCABC there are 3 publications, for ABCDDD there are 4.
>
> A B C D
> ABC ABCABC A MELAGE
> ABC ABCABC A MELAGE
> ABC ABCABC A MELHER
> ABC ABCABC A DOGHAN
> ABC ABCDDD A MELAGE
> ABC ABCDDD A MELAGE
> ABC ABCDDD A SYDMOR
> ABC ABCDDD A MELHER
> ABC ABCDDD A BRICOU
>
> In a separate area I will then have a list of B codes with the formula next
> to it that calculates the number of unique publications eg:
>
> ABCABC 3
> ABCDDD 4
>
> Any help with the formula I should use would be greatly appreciated.

That works wonderfully - thanks heaps

"Domenic" wrote:

> Here's another way...
>
> Assuming that F1:F2 contains ABCABC and ABCDDD...
>
> G1, copied down:
>
> =SUM(IF(FREQUENCY(IF($B$1:$B$9=F1,MATCH($D$1:$D$9,$D$1:$D$9,0)),ROW($D$1:
> $D$9)-ROW($D$1)+1)>0,1))
>
> or
>
> =COUNT(1/FREQUENCY(IF($B$1:$B$9=F1,MATCH($D$1:$D$9,$D$1:$D$9,0)),ROW($D$1
> :$D$9)-ROW($D$1)+1))
>
> Both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just
> ENTER.
>
> Hope this helps!
>
> In article <[email protected]>,
> "BeSmart" <[email protected]> wrote:
>
> > I have a list of 900 entries and for each change of code in column B I need
> > to know the number of publications used in D excluding the duplication, ie
> > for ABCABC there are 3 publications, for ABCDDD there are 4.
> >
> > A B C D
> > ABC ABCABC A MELAGE
> > ABC ABCABC A MELAGE
> > ABC ABCABC A MELHER
> > ABC ABCABC A DOGHAN
> > ABC ABCDDD A MELAGE
> > ABC ABCDDD A MELAGE
> > ABC ABCDDD A SYDMOR
> > ABC ABCDDD A MELHER
> > ABC ABCDDD A BRICOU
> >
> > In a separate area I will then have a list of B codes with the formula next
> > to it that calculates the number of unique publications eg:
> >
> > ABCABC 3
> > ABCDDD 4
> >
> > Any help with the formula I should use would be greatly appreciated.
>

On Mon, 29 Aug 2005 20:33:10 -0700, "BeSmart"
<[email protected]> wrote:

>The result I got only told me how many times ABCABC occurs in the list.
>
>What I need to know is:
>
>"For everything in column B that says ABCABC, count and report the number of
>different publications listed in column D.
>
>Do I need to define name the column D list and call it "publications"?

I'm not sure what you are doing differently than I.

You can either NAME the appropriate ranges (Code=$B$2:$B$n
Publications=$D$2:$D$n) or use the cell references in their place in the
formula.

So if your table were in A2:D10, the formula could read:

=COUNTDIFF(IF($B$2:$B$10=H1,$D$2:$D$10),,FALSE)

again -- entered as an ARRAY formula.


--ron