Ignoring blank cells while locating common text value

[Guest]

I hope that I have been able to make this clear enough

1. First thing I am trying to do. I have a column of cells that have
multiple values, some with text and some with no values at all. I want to be
able to display in A1 the most commonly occurring text in cells C1:C15, and
be able to display in B1 the number of times that A1 occurs in the same
range. Below are the formulas that I am using. There are two problems that I
am running into: First, the formula returns a #NA error if any of the cells
in the range are left blank. Second, the formula counts the spaces or zeros,
so if there are more blanks than the word “amber†then A1 returns “ †and B1
returns the corresponding number.

A1
=INDEX(C1:C15,(MODE(MATCH(C1:C15,C1:C15,0))))

B1
=COUNTIF(C1:C16,A1)

2. Second thing I am trying to do. In A2 I want to display the second most
commonly occurring text in the range, with it’s corresponding count in cell
B2, and the third most in A3 and B3, etc

Illustration:

C1 Amber
C2 Red
C3
C4
C5
C6 Red

Desired result:

A1 "Red" B1 "2"
A2 "Amber" B2 "1"

Results with forumla as posted

A1 " " B1 "3"

Any help would be greatly appreciated

Thanks in advance

 

[Bernie Deitrick]

You need a helper column of formulas: insert a blank column D, and in cell
D1, enter the formula

=IF(AND(C1<>"",C1<>"
"),IF(COUNTIF($C$1:$C$15,C1)=COUNTIF($C1:$C$15,C1),COUNTIF($C$1:$C$15,C1)+ROW()/100,""),"")

and copy down to D15. Then in cell A1, enter the formula

=INDEX($C$1:$C$15,MATCH(LARGE(D,ROW()),D,FALSE))

and copy down for as far as you want. Keep the formulas in B:B the same.

HTH,
Bernie
MS Exel MVP