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

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

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

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

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

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