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