PDA

View Full Version : Solved: Match

zoom38
02-15-2007, 05:23 PM
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
02-15-2007, 06:20 PM
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
02-15-2007, 07:37 PM
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.aspx?scid=kb;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.
The text passage describes a user seeking help with Excel formulas to correctly list the top 5 most frequently occurring values, even when there are ties. The user provides their initial formulas and explains the issue, then shares a solution they found for ranking duplicate values.
no_question

while the passage discusses a problem and its solution, it does not explicitly pose a mathematical question or request for an answer. the solutions are provided by the user themselves, rather than being answers to a specific question posed in the text.
Given a text passage, determine:
    1. If it contains a mathematical question (including word problems or calculations)
    2. If an answer to that mathematical question is provided in the text

First, describe the content structure you found in 1-2 sentences.

Then provide your analysis in the following format:
TYPE: <one of the following values>
- NO_QUESTION
- SINGLE_QUESTION
- SINGLE_QUESTION_WITH_ANSWER
- MULTIPLE_QUESTIONS
- MULTIPLE_QUESTIONS_WITH_ANSWERS
system
PDA

View Full Version : Solved: Match

zoom38
02-15-2007, 05:23 PM
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
02-15-2007, 06:20 PM
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
02-15-2007, 07:37 PM
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.aspx?scid=kb;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.
user
The text passage describes a user seeking help with Excel formulas to correctly list the top 5 most frequently occurring values, even when there are ties. The user provides their initial formulas and explains the issue, then shares a solution they found for ranking duplicate values.

TYPE: NO_QUESTION

While the passage discusses a problem and its solution, it does not explicitly pose a mathematical question or request for an answer. The solutions are provided by the user themselves, rather than being answers to a specific question posed in the text.