i want to only select one name from a list of the same names
 

I have a column of names and in the column there are 20 rows saying "apples"
and 10 rows saying "oranges" and 5 rows of "bannanas". I would like to
produce a report that pulls only the name i.e. "apples", which would then
allow me to produce a consolidated list of names. This would mean that I
would end up with 3 rows one saying "apples", one saying "oranges and one
saying "bannanas".

i want to only select one name from a list of the same names
 

In B1 use =A1
In B2, add

=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2
0&""),0)))

which is an array formula, so commit with C trl-Shift-Enter, then copy down.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"rhinozw" wrote in message
...
I have a column of names and in the column there are 20 rows saying
"apples"
and 10 rows saying "oranges" and 5 rows of "bannanas". I would like to
produce a report that pulls only the name i.e. "apples", which would then
allow me to produce a consolidated list of names. This would mean that I
would end up with 3 rows one saying "apples", one saying "oranges and one
saying "bannanas".

i want to only select one name from a list of the same names
 

Hii Bob,

Now that we have that working the next step I now can't figure out is that
each row of fruit has a value next to it and I would like to add up each row
so that when the formula you gave me consolidates the fruit into types then
it will add it up to? Is this possible?

"Bob Phillips" wrote:

In B1 use =A1
In B2, add

=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2
0&""),0)))

which is an array formula, so commit with C trl-Shift-Enter, then copy down.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"rhinozw" wrote in message
...
I have a column of names and in the column there are 20 rows saying
"apples"
and 10 rows saying "oranges" and 5 rows of "bannanas". I would like to
produce a report that pulls only the name i.e. "apples", which would then
allow me to produce a consolidated list of names. This would mean that I
would end up with 3 rows one saying "apples", one saying "oranges and one
saying "bannanas".

i want to only select one name from a list of the same names
 

In C1

=countif(A:A,B1)

and copy down

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"rhinozw" wrote in message
...
Hii Bob,

Now that we have that working the next step I now can't figure out is that
each row of fruit has a value next to it and I would like to add up each
row
so that when the formula you gave me consolidates the fruit into types
then
it will add it up to? Is this possible?

"Bob Phillips" wrote:

In B1 use =A1
In B2, add

=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"",

INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2
0&""),0)))

which is an array formula, so commit with C trl-Shift-Enter, then copy
down.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"rhinozw" wrote in message
...
I have a column of names and in the column there are 20 rows saying
"apples"
and 10 rows saying "oranges" and 5 rows of "bannanas". I would like to
produce a report that pulls only the name i.e. "apples", which would
then
allow me to produce a consolidated list of names. This would mean
that I
would end up with 3 rows one saying "apples", one saying "oranges and
one
saying "bannanas".

i want to only select one name from a list of the same names
 

Hey Bob - You're the man thank you!

"Bob Phillips" wrote:

In B1 use =A1
In B2, add

=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2
0&""),0)))

which is an array formula, so commit with C trl-Shift-Enter, then copy down.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"rhinozw" wrote in message
...
I have a column of names and in the column there are 20 rows saying
"apples"
and 10 rows saying "oranges" and 5 rows of "bannanas". I would like to
produce a report that pulls only the name i.e. "apples", which would then
allow me to produce a consolidated list of names. This would mean that I
would end up with 3 rows one saying "apples", one saying "oranges and one
saying "bannanas".

i want to only select one name from a list of the same names
 

In a similar instance:

If the column has numbers or small cap text along with large cap text. Is
it possible to return only the large cap text?



"Bob Phillips" wrote:

In B1 use =A1
In B2, add

=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2
0&""),0)))

which is an array formula, so commit with C trl-Shift-Enter, then copy down.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"rhinozw" wrote in message
...
I have a column of names and in the column there are 20 rows saying
"apples"
and 10 rows saying "oranges" and 5 rows of "bannanas". I would like to
produce a report that pulls only the name i.e. "apples", which would then
allow me to produce a consolidated list of names. This would mean that I
would end up with 3 rows one saying "apples", one saying "oranges and one
saying "bannanas".