List of repeating names

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

 

[Debra Dalgleish]

You can use an Advanced Filter to export a unique list of items. There
are instructions in Excel's Help, and here:

http://www.contextures.com/xladvfilter01.html

 

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

 

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

 

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

 

[johnT]

this works.....thanks again Bob, and sorry for all the
hassle!

 

[Bob Phillips]

Hi John,

If it was a hassle, I could ignore it. It's fun

Bob

 

[Aladin Akyurek]

A fast formula system:

http://tinyurl.com/5gdun