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" <BeSmart@discussions.microsoft.com> wrote in message
news:908B8CAE-ECCC-4AA9-ABB0-203914BC7834@microsoft.com...
> 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

On Mon, 29 Aug 2005 20:33:10 -0700, "BeSmart"
<BeSmart@discussions.microsoft.com> 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

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 <908B8CAE-ECCC-4AA9-ABB0-203914BC7834@microsoft.com>,
> "BeSmart" <BeSmart@discussions.microsoft.com> 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 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 <908B8CAE-ECCC-4AA9-ABB0-203914BC7834@microsoft.com>,
"BeSmart" <BeSmart@discussions.microsoft.com> 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.

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"
> <BeSmart@discussions.microsoft.com> 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
>

On Mon, 29 Aug 2005 17:33:16 -0700, "BeSmart"
<BeSmart@discussions.microsoft.com> 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

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
--

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
--

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 <908B8CAE-ECCC-4AA9-ABB0-203914BC7834@microsoft.com>,
> "BeSmart" <BeSmart@discussions.microsoft.com> 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 17:33:16 -0700, "BeSmart"
<BeSmart@discussions.microsoft.com> 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

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" <BeSmart@discussions.microsoft.com> wrote in message
news:908B8CAE-ECCC-4AA9-ABB0-203914BC7834@microsoft.com...
> 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

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"
> <BeSmart@discussions.microsoft.com> 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
>

On Mon, 29 Aug 2005 20:33:10 -0700, "BeSmart"
<BeSmart@discussions.microsoft.com> 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

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 <908B8CAE-ECCC-4AA9-ABB0-203914BC7834@microsoft.com>,
"BeSmart" <BeSmart@discussions.microsoft.com> 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.

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
--

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" <BeSmart@discussions.microsoft.com> wrote in message
news:908B8CAE-ECCC-4AA9-ABB0-203914BC7834@microsoft.com...
> 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

On Mon, 29 Aug 2005 17:33:16 -0700, "BeSmart"
<BeSmart@discussions.microsoft.com> 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

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 <908B8CAE-ECCC-4AA9-ABB0-203914BC7834@microsoft.com>,
> "BeSmart" <BeSmart@discussions.microsoft.com> 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"
<BeSmart@discussions.microsoft.com> 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

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"
> <BeSmart@discussions.microsoft.com> 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 <908B8CAE-ECCC-4AA9-ABB0-203914BC7834@microsoft.com>,
"BeSmart" <BeSmart@discussions.microsoft.com> 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 <908B8CAE-ECCC-4AA9-ABB0-203914BC7834@microsoft.com>,
> "BeSmart" <BeSmart@discussions.microsoft.com> 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.
>

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" <BeSmart@discussions.microsoft.com> wrote in message
news:908B8CAE-ECCC-4AA9-ABB0-203914BC7834@microsoft.com...
> 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

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 <908B8CAE-ECCC-4AA9-ABB0-203914BC7834@microsoft.com>,
"BeSmart" <BeSmart@discussions.microsoft.com> 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.

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"
> <BeSmart@discussions.microsoft.com> 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
>

On Mon, 29 Aug 2005 20:33:10 -0700, "BeSmart"
<BeSmart@discussions.microsoft.com> 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

On Mon, 29 Aug 2005 17:33:16 -0700, "BeSmart"
<BeSmart@discussions.microsoft.com> 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

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 20:33:10 -0700, "BeSmart"
<BeSmart@discussions.microsoft.com> 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

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 <908B8CAE-ECCC-4AA9-ABB0-203914BC7834@microsoft.com>,
> "BeSmart" <BeSmart@discussions.microsoft.com> 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 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 <908B8CAE-ECCC-4AA9-ABB0-203914BC7834@microsoft.com>,
"BeSmart" <BeSmart@discussions.microsoft.com> 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.

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" <BeSmart@discussions.microsoft.com> wrote in message
news:908B8CAE-ECCC-4AA9-ABB0-203914BC7834@microsoft.com...
> 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

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"
> <BeSmart@discussions.microsoft.com> 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
>

On Mon, 29 Aug 2005 17:33:16 -0700, "BeSmart"
<BeSmart@discussions.microsoft.com> 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

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
--