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

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" <rhinozw@discussions.microsoft.com> wrote in message
news:C02254D4-0083-42F5-97A7-F3B6D330BCE2@microsoft.com...
> 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" <rhinozw@discussions.microsoft.com> wrote in message
> news:C02254D4-0083-42F5-97A7-F3B6D330BCE2@microsoft.com...
> > 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" <rhinozw@discussions.microsoft.com> wrote in message
news:E194B0BA-75B4-4760-AFA0-499B4E60021D@microsoft.com...
> 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" <rhinozw@discussions.microsoft.com> wrote in message
> > news:C02254D4-0083-42F5-97A7-F3B6D330BCE2@microsoft.com...
> > > 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" <rhinozw@discussions.microsoft.com> wrote in message
> news:C02254D4-0083-42F5-97A7-F3B6D330BCE2@microsoft.com...
> > 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".
>
>
>