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

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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]

In C1

=countif(A:A,B1)

and copy down

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Hey Bob - You're the man thank you!

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)

 

[Jay]

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?