Count unique entries
 

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" wrote in message
...
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"
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"
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 ,
"BeSmart" 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 ,
"BeSmart" 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"
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