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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rhinozw - 09 Dec 2005 08:16 GMT

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

Reply to this Message

Bob Phillips - 09 Dec 2005 09:11 GMT

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.

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

Reply to this Message

rhinozw - 09 Dec 2005 14:19 GMT

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?

> In B1 use =A1
> In B2, add
[quoted text clipped - 12 lines]
> > would end up with 3 rows one saying "apples", one saying "oranges and one
> > saying "bannanas".

Reply to this Message

Bob Phillips - 09 Dec 2005 15:41 GMT

In C1

=countif(A:A,B1)

and copy down

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> Hii Bob,
>
[quoted text clipped - 7 lines]
> >
> > =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)))
> >
[quoted text clipped - 7 lines]
> > > would end up with 3 rows one saying "apples", one saying "oranges and one
> > > saying "bannanas".

Reply to this Message

rhinozw - 09 Dec 2005 20:12 GMT

Hey Bob - You're the man thank you!

> In B1 use =A1
> In B2, add
[quoted text clipped - 12 lines]
> > would end up with 3 rows one saying "apples", one saying "oranges and one
> > saying "bannanas".

Reply to this Message

Jay - 21 May 2008 19:20 GMT

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?

> In B1 use =A1
> In B2, add
[quoted text clipped - 12 lines]
> > would end up with 3 rows one saying "apples", one saying "oranges and one
> > saying "bannanas".

Reply to this Message