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.