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

[@elphau]

Hi Mike, I've been following your Excel for quite sometime now. My boss said I'm the Excel expert in the office, and I owed it you a lot. cos, i believe 80% of my excel skill come from watching your video. just wanna say thank you.

[@Samy991188]

Man, you probably have no idea how much this helped me out too :( , I was on the brink of going crazy before I found this. So thanks a lot! i'm following now all your videos.... thanksssssssssssss

[@excelisfun]

You are welcome, Abdelsalam!!!!

[@Samy991188]

@@excelisfun thanks

[@DannGillen]

Really well done! Thank you for your clarity! You rock!

[@excelisfun]

You are welcome!!!

[@DannGillen]

​@@excelisfun Hello! I've deployed this in a cool poker calculator I have created, but have hit a roadblock with a next level application of this. I have gone to town on it and found some success, but keep slamming into an issue that I'm hoping you could help with. Would you be available to help me with this? Would email be best? Here's a general description: I have found a situation where I need to remove lines of the sorted rows based on a few conditions. I went through many IF/AND/ORs and many combinations thereof. I was successful in removing the rows that meet the conditions, but now the issue is that I cannot figure out how to elevate (if you will) the remaining rows to the top of the sorted list. Or how to re-sort the new list. I hope that's clear enough.

[@Sumiyeco_boutique]

This guy is a great presenter. Clear speech.

[@mariachica2741]

you saved my life! THANKS!

[@excelisfun]

You are welcome!

[@askni1985]

There should not be any dislike button for all videos on this channel, thank you, your videos are much appreciated and there is a request, please make videos like this on access also

[@iplaystuff2465]

Man, you probably have no idea how much this helped me out. I was on the brink of going crazy before I found this. So thanks a lot!

[@hogenkkalabhunk1798]

Tee

[@63ALEXGO]

Mike instead of select the formula manual before pressing f9 to show the result of your formula you may select the section of the screen tip, excel will select the formula for you example in if(a1=b1,c1) you don't have to select the a1=b1 manually before pressing f9 in screen tip (if(logical test,value if true,value if false) just select logical test in the screen tip then excel will select a1=b1 for you you are a great teacher I learn a lots from your videos million thanks

[@daffy.]

Ah man, this has literally saved my whole spreadsheet model. Thank you so much!

[@SANDYRUPA2006]

I feel he is a great Teacher very easy to understand Keep it up Thanks a lot give us about more about Access

[@at-excel]

The formulas are finished. I modified it, to show the ranks not in order 1,2,3,4,5,6 but in 1,2,2,4,5,5 if there are some ties. I used a time list and added an additional Rank.eq in Column C C6: =Rank.eq(a6;$a$6:$a$12;1) D6: =if(rows($d$6:d6)

[@Comehometoday]

Great video! saves me whole day trying to figure out how to extract top 10 numbers with names from a pivot table

[@excelisfun]

I am glad that you liked it!

[@aashishadhikari7194]

You are too brilliant, no word to explain you how much you helped me, thanks dear

[@excelisfun]

Glad the videos help, Aashish!!! Thank you for your support with your comment, Thumbs Up and Sub : )

[@excelisfun]

I am glad that the video helps!

[@gustavoutz21]

Wow this video saved my day. thank you!

[@excelisfun]

You are welcome! I am happy to help!

[@excelisfun]

I am glad that you like it!

[@excelisfun]

You are welcome! I am happy it helps!

[@excelisfun]

Cool! I am glad that you liked the video!

[@excelisfun]

Wow!!!! That is TOTALLY amazing!!! I had no idea that it could work that way. Thanks for the great trick. I can't wait to make a video to display your amazing trick!!

[@omarrivera0108]

Absolutely love learning in this specific modular approach. You are definitely a magician with this stuff! I'm so glad I came across your channel. Greatest example of knowledge sharing! You're making such a difference. THANK YOU!

[@markmoscosa2371]

That trick was Fantastic Mike. Admire all your passion for Excel and to share this knowledge with the Crew !

[@excelisfun]

Cool! I am glad that you like this video!

[@ammarazizi9962]

thank you so much! this really help me to analyze data much much faster!

[@excelisfun]

Glad this helps, Ammar!!!

[@at-excel]

Thanks a lot for your comments on my KZbin channel. Now I'm looking for a way to show the correct ranks without column C. Still waiting for your next video, Andreas.

[@AhmadMustafaMohmedMetwalley]

you are amizing ...this is summery for hundred of your vedios....thanks so much

[@lucaviglio1206]

Amazing video... So helpful.. Thanks a lot Mike

[@excelisfun]

Glad I can help : )

[@vipuljhaveri4805]

Thank a lot , for your brilliant and lucid explanation , well advanced tricks made very easy for all with perfect presentation and with its download files to learn , practice and use , you are a unique mentor ! ! !

[@excelisfun]

Glad to help, vipul!!!!

[@RobertLongM]

What an amazing teacher you are!

[@excelisfun]

Glad you like it, Robert! Thanks for your support : )

[@Innovlrnr]

I am trying to consume this very valuable knowledge , and scratching my head too as it's not easily understandable for novice like me

[@fernleystephens2436]

I've volunteered to be the treasurer of a local organization and have been busy building a spreadsheet to help me track our finances. I think of a feature I'd like to add but have no idea how to implement it but all I have to do is look through your videos and I'll find the answer. Thanks a lot for your help.

[@excelisfun]

You are welcome for the knowledge!

[@827stormin]

this video is beyond excellent. really helped me out on some of my projects. totally awesome. does a great job explaining it in easy follow along style. these current formulas and the set up is perfect probably for most people. hope him or someone would please post formula in the rank column to make it: 1,1,3,4,5,5,5,8, etc... with this added would make it complete and i believe a lot of people would really love it. the best without dispute.

[@excelisfun]

Yes, you can make a re-make of it in German! Excelisfunner says hi to you!

[@diamanthaxhimusa8103]

You are amazing teacher. I admire you , you make me to love ,to woik in excel .. Thank you

[@excelisfun]

Wow! Beautiful setup with the RANK! I can't wait to see your video!

[@fajardiniarikamil2712]

i did it ,it works and i dont know how it work but it gives me what i want

[@SyedMuzammilMahasanShahi]

Wow. That is amazing. Thanks Mike for this EXCELlent video.

[@excelisfun]

I am glad that you like it, krn14142! Yes, I changed the production for the mic because I had some "pops" in this particular video. I also produced in HD - but it took 20 times longer (literally instead of producing in 1 - 2 minutes it took 20 to 30...)

[@excelisfun]

You are welcome!

[@excelisfun]

It does seem weird, but it is the syntax that the COUNTIF function requires in this situation.

[@TruongTammie]

You are awesome, the video is just exactly how i need it. Thank you so much!!!!

[@levans13]

Thank you for sharing this information! Worked perfectly!!!

[@ryanbauer3304]

AWESOME!!! Thank you so much. I have been trying to figure this out for like a year :D

[@RohitKumar-cc7zi]

Welcome Owsome bro just keep it up 👍👍👍👍 fabulous

[@excelisfun]

: ) : ) : ) : )

[@valeriamarano2188]

I Love you!!!!! I've saved so many time!! You make my day!!

[@dumpalakalki6897]

Thank you sir today I learned new things from you

[@anil11996]

Your work is excellence

[@JavyD]

This was amazing. Great Job! One of my favorites.

[@KennethTranMD]

Wow, thanks a lot. That's very helpful.

[@excelisfun]

You are very welcome, Kenneth!

[@KennethTranMD]

@@excelisfun if i want D6:D10 to show 1,2,3,4,4 instead of 1,2,3,4,5. How could I do that? thanks

[@ganeshkumarsit8671]

Thanks a lot. It helped me very much

[@excelisfun]

You are welcome, a lot! Thanks for your support with your comment, thumbs up and Sub : )

[@s.kishoredina6241]

Great wrk 👍👍 thanks for your vedio

[@excelisfun]

You are welcome, s.kishore!!!

[@eLMaestroDos]

Thank you so much for this sir this is really great..

[@excelisfun]

You are welcome, jhiechel1!!!

[@askni1985]

Thank you for the links

[@6san6sei6]

amazing trick. now i can make pareto charts fast and easy

[@guilhermefilho]

You rule man!!

[@excelisfun]

That is so funny, this video is the video I am releasing tomorrow, Oct 4, 2019 as my Flash Back Friday. It is an oldie but a goodie...

[@shyamnilvk]

Mate really appreciated, you help me a lot buddy. Thank you very much.

[@alaypaun]

Dude, you just saved me hours of my life. Thanks for this, you're a legend :D

[@TheKinsayan]

Great thanks ! And great formula !

[@TheGK2009]

Thanks!! This series is very helpful. Please keep posting these excellent excel tricks and tutorial. Regards,, GK

[@vijaysahal4556]

nice sir 👍🏻👍🏻👍🏻👍🏻👍🏻

[@prince4x4]

Thanks u safe my life.

[@excelisfun]

Glad the video helps, Niti!!!!

[@at-excel]

Thanks for the great tutorial (again). I would like to produce a german remake of it. Greetings from Germany - also to ExcelIsFunner

[@deepk82]

Great job, solved many problems :-)

[@excelisfun]

That is too bad that you can't install 1013. But no worries, Excel 2010 or 2007 still are TOTALLY amazing!!!

[@susanwhite5009]

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!

[@iftekharraju318]

excellent...thanks man..

[@havzz19]

Hi, this is a really great show of how to use some of the functions! Will definitely be using this demo to help set up my data! Thanks :-))))

[@muraligonti8999]

Grate formula... Tq

[@krn14242]

Great comparison Mike. Your voice sounds a little different, better less background noise. Did you get a new mic or something? Thanks.

[@AVielot]

Mike, wonderful video!.. I was wondering if you could maybe try a video of adding an additional criteria and then extract based on this additional constraint? For example, lets select the top 5 but eliminate the "Max" because it meets the criteria of an "outlier" ... resulting in an extract of only 4.... Scratching my head over this... but still trying to figure this out...

[@excelisfun]

Dates are serial numbers, so just use the number part of this video.

[@ShahabUddin-qh5bl]

Great video Sir, I am trying return value matching different columns but row function doesn't help

[@spawar7586]

Very Good Sir, Its nice I like this. But I find the difficulty if we have same number then the name is repeating. Who we can over come if we are using Ms office 2013. Please help regrading this....

[@YotesFan1980]

Great video! It helped me with a leaderboard for a baseball stat. I have run into one tiny problem. How do I create a restriction with what you're doing at roughly the 8:00 mark to make a minimum appearance requirement? If, say, a pitcher needs 15 appearances to qualify for a percentage stat? With what I'm trying, the function returns the first guy it sees with the top-ranking percentage, despite the fact that he has only one appearance. Thank you in advance!

[@ahmedalturkey7864]

You are awesome

[@alializadeh8195]

Thanx

[@shekhvasim249]

What to do when in top ten more than 1 duplicate value

[@gregs_b]

LEGEND

[@excelisfun]

9 years back : )

[@karolinaunderwood1039]

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".

[@buriskad]

Is there a way to extract top values for displayed cells only? Amazing lesson btw, thanks!

[@euanstenson6293]

big up, v helpful

[@coolkidz33]

At the 2:56 mark, I do not understand why you cannot just put =countif(A6:a12>=B2). Why does MS make us put it in quotes and then join it together? Seems like Excel should just be able to recognize the >= portion of the formula without making it ">="&. Really great video BTW.

[@excelisfun]

You do not want to use IFERROR rather then IF. Why? Because with an alternative logical test and the IF, the formula will not have to run the big array formula in every cell; with IFERROR the formula must run the whole formula in every cell. Although for small data sets, the calculation time saved is not noticeable, for large sets it is dramatic. It is efficient to use IF instead of IFERROR . Here is a video: Excel Magic Trick 1029: DON'T Use IFERROR, Use IF and Logical Test If Possible...

[@norsalwati9138]

Thank you so much this really helps me listing all the names with the tie marks. But can you please advise how do we do if we also want to rank the name of the tie marks based on other condition. For the example in your case, Moji and Hafiz have the same marks. can i ranking Moji and Hafiz based on other condition such as other marks? Thank you.

[@TheAnandcv]

It is really nice. I could not get excel file. please give me exact link

[@mybadongt613]

On a different note, what if there are 3, 4 or 5 names or data that are the same instead of 2 same names as in the example? How would it be coded? Do you have a video of a variation of this??

[@adnantahir4530]

Dear, please tell how can we use small function in the same sheet by leaving zero and counting from above zero

[@barrieracingclub7097]

This is amazing, i want to know if you have 5 times of the same manager with all different visit can he calculate from each manager and after rank them ?

[@danmuir8626]

Really good video but I have a question. What do you do if you have more than 2 duplicates? It comes up as an error every time.

[@rda51]

Another fantastic video. Thanks again Mike. And 63ALEXGO - great tip! Thanks to you too.

[@jeffwalls4530]

Is there a way to put the number of visits in descending order instead of ascending order? In your example it would rank the least number of visits 1st and the most visits last.

[@MichaelByrneWX]

Is there a way to add date criteria also, I want to show two lists in my dashboard report, Top 10 last month and Top 10 last year. I refresh my data base table of transactions several times per month. p.s. I've learned fantastic tips and tricks from you over the years

[@mybadongt613]

Do u have a video showing matching dates instead of Names??

[@jimgaylord1004]

Hi, great video. I posted a message but do not see it, so here it is again, hopefully not twice. Trick 967 is what I require for our baseball sandbag team to keep track of top players, etc, we have men and women and I would like to have a list of top 5 men players and a list of top 5 women players. I've tried adding a if statement but can't get it to work. What needs to be done in the formula to do this?

[@MrHeinsryan]

Hi this is a great video, but I do have a quick question: How would you go about filtering via formula top 2 managers that contain names starting with the letter M? This is hypothetical and relative to what I'm trying to do. I'm trying to pull out the top performers of 2012, when 2011 is also listed in the same range. I'm having trouble leaving the 2011 top performers out and can't solve it on my own. All help is appreciated! Ryan

[@andersbruun1272]

Hi, and thanks for a great video, it was a huge help for me. An additional question, is it possible in any way to add a secondary criteria, when making the top 5? Fx. when shooting clay targets. 20 hits in 20 shots. Then 20/21, 20/22, 19/20 and 19/21. That's the correct order, obviously 20/20 is better than 20/21. How can I make excel sort it out for me? Regards, Anders Bruun

[@ryanpreston6508]

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?

[@akademiks]

Thanks for this tutorial.. I've got a question though. I'm having a difficult time logically putting together & branching Excel functions/formulas together.. Do you have any videos or PDFs where you basically go through the fundamentals of this? Right now, I'm at the point where I'm only really able to put multiple functions together by memory, and I'd really like to get a stronger understanding..