text: 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.
explanation: The document is a forum thread discussing a problem with Excel formulas used to find the top 5 most frequently occurring values in a column. The user, zoom38, initially describes the issue and provides the formulas they are using. Shazam offers a solution, but it does not fully meet the user's needs. Zoom38 then provides a solution they found, which involves using array formulas and non-array formulas from Microsoft Support and Pearson's site.
  
  The document contains a question and an answer.
distilabel_metadata.content: Evaluate the user given text extract and determine if it contains a math question and optionally and answer to this question.
  
  Output rules:
  
  1. Briefly describe the document and mention if it contains a question or a question and answer.
  2. Finalize the output with: Label: no-question,question,question-and-answer,multiple-question,multiple-question-and-answer
distilabel_metadata.role: system
distilabel_metadata.content: 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.
distilabel_metadata.role: user
distilabel_metadata.raw_output_q_a_detector_0: The document is a forum thread discussing a problem with Excel formulas used to find the top 5 most frequently occurring values in a column. The user, zoom38, initially describes the issue and provides the formulas they are using. Shazam offers a solution, but it does not fully meet the user's needs. Zoom38 then provides a solution they found, which involves using array formulas and non-array formulas from Microsoft Support and Pearson's site.
  
  The document contains a question and an answer.
  
  Label: question-and-answer