John,

You need

=IF(ROW(Sheet1!A2)-ROW(Sheet1!$A$2)+1(COUNTIF(Sheet1!$A$2:$A$102,$Z$ 1)+COUN
TIF(Sheet1!$A$2:$A$102,$Z$2)+COUNTIF(Sheet1!$A$2:$ A$102,$Z$3)),"",SMALL(IF(S
heet1!$A$2:$A$102=TRANSPOSE($Z$1:$Z$3),ROW(Sheet1! $A$2:$A$102),""),ROW(Sheet
1!A2)-ROW(Sheet1!$A$2)+1))

If the comparison cells are in a row rather than a column, then ditch the
TRANSPOSE.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"johnT" wrote in message
...
not exactly the same question....anyway....in your
formula:

=IF(ROW(Sheet1!A2)-ROW(Sheet1!$A$2)+1(COUNTIF(Sheet1!
$A$2:$A$102,"Bob")+COU
NTIF(Sheet1!$A$2:$A$102,"Jim")+COUNTIF(Sheet1!
$A$2:$A$102,"Dave")),"",SMALL(
IF(Sheet1!$A$2:$A$102={"Bob","Jim","Dave"},ROW(She et1!
$A$2:$A$102),""),ROW(S
heet1!A2)-ROW(Sheet1!$A$2)+1))

is there a way of replacing {"Bob","Jim","Dave"} with
cell referances {z1,z2,z3} this doesn't seem to work

(thank you)




-----Original Message-----
Didn't we answer this for you yesterday? You seemed
quite happy with the
answers then.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"johnT" wrote in
message
...
Hello,

I believe this is probably a simple one, yet seems to
excape me at the moment.....I have a long column of
names,
often repeating names on sheet1....I would to make a
shorter list on sheet2 of all the names in the column,
but
only list them once.

Thanks again for all your good ideas!


.