ExcelBanter - List of repeating names

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - List of repeating names (https://www.excelbanter.com/excel-worksheet-functions/19559-list-repeating-names.html)

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 he

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

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!

.

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!

.

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

.

.

Hi John,

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

Bob

"johnT" wrote in message
...
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" 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!

.

.

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!