List of repeating names

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!

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


johnT wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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" <[email protected]> wrote in message
news:[email protected]...
> 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!

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)




>-----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" <[email protected]> wrote in
message
>news:[email protected]...
>> 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!
>
>
>.
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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)
>
>
>
>
> >-----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" <[email protected]> wrote in
> message
> >news:[email protected]...
> >> 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!
> >
> >
> >.
> >

this works.....thanks again Bob, and sorry for all the
hassle!
>-----Original Message-----
>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" <[email protected]> wrote in
message
>news:[email protected]...
>> 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)
>>
>>
>>
>>
>> >-----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" <[email protected]> wrote in
>> message
>> >news:[email protected]...
>> >> 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!
>> >
>> >
>> >.
>> >
>
>
>.
>

Hi John,

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

Bob


"johnT" <[email protected]> wrote in message
news:[email protected]...
> this works.....thanks again Bob, and sorry for all the
> hassle!
> >-----Original Message-----
> >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" <[email protected]> wrote in
> message
> >news:[email protected]...
> >> 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)
> >>
> >>
> >>
> >>
> >> >-----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" <[email protected]> wrote in
> >> message
> >> >news:[email protected]...
> >> >> 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!
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

A fast formula system:

http://tinyurl.com/5gdun

johnT wrote:
> 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!