List of repeating names

J

johnT

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

Bob Phillips

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

johnT

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(Sheet1!
$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)
 
B

Bob Phillips

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