Solved: Match

[zoom38]

I have a column which has the number of cells that contain specific data.
Im using the following formulas to get the top 5 values that occur most frequently.
= INDEX($E$3:$E$7,MATCH(LARGE($F$3:$F$7,1),$F$3:$F$7,0))
= INDEX($E$3:$E$7,MATCH(LARGE($F$3:$F$7,2),$F$3:$F$7,0))
= INDEX($E$3:$E$7,MATCH(LARGE($F$3:$F$7,3),$F$3:$F$7,0))
= INDEX($E$3:$E$7,MATCH(LARGE($F$3:$F$7,4),$F$3:$F$7,0))
= INDEX($E$3:$E$7,MATCH(LARGE($F$3:$F$7,5),$F$3:$F$7,0))

My problem is that if there are more than one value that occurs the same amount of times, the formulas will only list the first value.

ex: I have the following top 5 values in column F
17
18
15
17
8

The corresponding values to the above in column E are:
red
white
blue
yellow
pink
My formulas will return white, red, red, blue, pink.
The formulas will only return the corresponding value for the first instance of 17 which is red. Yellow which is also 17 gets skipped. How can I avoid this?

[Shazam]

Maybe this might work for you. How about putting those text in the same column so you dont have to use 4 formulas to get you result.


Try...

Put all your text column A needs to be sorted.

Then input formula in cell D3 and copied down:

=LARGE(IF(A$3:A$99<>A$4:A$100,COUNTIF(A$4:A$100,A$4:A$100)),ROWS(D$3:D3))

Now input formula in cell C3 and copied down:

=INDEX($A$4:$A$100,MATCH(TRUE,IF(COUNTIF(C$2:C2,$A$4:$A$100)=0,D3=COUNTIF($ A$4:$A$100,$A$4:$A$100)),0),1)


Both formulas are an-array.

Need to hold down:

Ctrl,Shift,Enter.


I attach a sample workbook below.

[zoom38]

Shazam thanks for the reply but not quite what im looking for. The attachment was just a testing sheet and I envision using it for imported data that will not be sorted and placed all in one column. In fact the data will be in multiple columns. I did find the following array formula that seems to do the trick, at least on a simple test. It assigns a unique value to data with duplicates.

=SUM(1*($F3>$F$3:$F$7))+1+IF(ROW($F3)-ROW($F$3)=0,0,SUM(1*($F3=OFFSET($F$3,0,0,INDEX(ROW($F3)-ROW($F$3)+1,1)-1,1))))

This can be found at
http://support.microsoft.com/default...b;en-us;152567
titled "How to Rank Duplicate Values Sequentially".

Thanks again.
Gary

Better yet I found the following non-array formulas on Pearsons site at http://www.cpearson.com/excel/rank.htm

Rank Highest To Lowest
=RANK(F3,F$3:F$7)+COUNTIF(F$3:F3,F3)-1

Rank Lowest To Highest
=COUNT(F$3:F$7)-(RANK(F3,F$3:F$7)+COUNTIF(F$3:F3,F3))+2

They work quite nicely.