Excel Magic Trick 967: Formula To Extract Top 5 Numbers & Names, Including Ties or Duplicates



Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm Concepts: 1. This video is great to learn how to use a formula to extract top 5 numbers (revenues, sales, units or sports scores) and the associated names (Sales Representatives, Employees or Athlete Names) including the situation when there are ties or duplicates in the numbers. 2. What to do when VLOOKUP can't do a lookup for duplicate values or ties and it is not possible to do a concatenated helper column; meaning there are not two lookup values, but instead just one lookup value (number value from LARGE). In this Video: 1. SMALL Function Example: for Excel 2007 or earlier array formula with the functions: LARGE, COUNTIF, IF, ROWS, INDEX, SMALL and ROW. Also see: array operations, comparative operators, join symbol ampersand, and expandable ranges. 2. AGGREGATE Function Example: Excel 2010 or 2013 array formula with the functions: LARGE, COUNTIF, IF, ROWS, INDEX, AGGREGATE and ROW. Also see: array operations, comparative operators, join symbol ampersand, syntax for show nothing or empty cell or null text string and expandable ranges. 3. How Come VLOOKUP will not work 4. Find Hurdle Value for Top 5 using LARGE function 5. Count how many values should be extracted using COUNTIF function, comparative operator and Ampersand (join symbol)

Comments

  1. excellent...thanks man..
  2. this is great! thank you so much for your help. Do you think you could also show us how to improve this formula with two variables for small function? I am using it for the Risk register to pull out Top 5 risks. However, I need to apply an additional condition that pulls out only Top 5 risks which have the status "open".
  3. What to do when in top ten more than 1 duplicate value
  4. OMG man, I thought you were Bosnian, because names like Zahid, Mirza, Hafiz, Mudassir are typical in Bosnia and Herzegovina and Moji means Mine in Bosnian hahahahahha
  5. Thank you thank you thank you for your help with this!!!!
  6. Thank you sir today I learned new things from you
  7. Hello - is it possible to have this work on the sum of a range? I.E If you have the same name repeat on another line, how could you have it sum the number of visits by the managers name and then rank?
  8. You are too brilliant, no word to explain you how much you helped me, thanks dear
  9. hello, i've been trying to figure out, if i have a 15 names in the cells for example from cell4 to cell19, how can i manage to highlight the name which is more repeated in the cells, thank you in advance for the reply.
  10. Thank you Sir.
  11. Ur amazing
  12. What if there are Duplicate names? There is no Duplicate name in the example...
  13. big up, v helpful
  14. PLEASE can someone send me the file, the link doesnt work anymore :( i will pay $10. please reach out to me
  15. Hiya. This video is awesome as it is the answer to what I desperately need. But, I need to use it in google sheets and cant quite get it to work. Can anyone assist with this? Many thanks in advance.
  16. Excellent video. So clear to follow and downloadable files to go along with the video... What could be better? I should've found this sooner.

    Thank you so much!
  17. hi, i did it step by step followed all your instructions, but when all the values is changed to zero, then zahid appears to all cells in the manager column. How do we remove that and leave a blank cell when there are no number inputs on the visits column?
  18. Hey! What is that Fuzzy Lookup??
  19. Hi sir in the case of the tie can we add criteria also for the ranking?Thanks
  20. Name not changes auto matic after change


Additional Information:

Visibility: 142758

Duration: 17m 34s

Rating: 689