Dynamically rank the top 10 values including handling tied values - kzread.info/dash/bejne/pZ9n3JqeYtDcnso.html This answers a common question in the comments thread for this video.

Of all the resources available on this subject I found yours to be most clear and helpful. God Bless!

@Computergaga

6 жыл бұрын

That is great to hear, thank you.

It's so satisfying finding an Excel tutorial that lets me do exactly what I wanted to do. Thank you!

@Computergaga

4 жыл бұрын

It is very satisfying to read comments like this. Thank you for watching.

I never get bored watching valuable videos and learning from you Alan. I am addicted to Excel and to such excellent videos. A good addiction, I think. Thank you. 👍

@Computergaga

5 жыл бұрын

You're welcome Salim. Thank you.

YOU ARE SO AWESOME!!! THANKYOU THIS WAS SUPER USEFUL. IM TYPING IN ALL CAPS BECAUSE IM SUPER EXCITED THAT I WAS ABLE TO DO THIS

@Computergaga

Жыл бұрын

Glad it helped! You're very welcome.

Thank you So Much, this is very helpful for me

@Computergaga

2 жыл бұрын

Fantastic! Glad it helped.

Thank you very much, this infact helping me in stock selection from a big database, the top 10.

@Computergaga

2 жыл бұрын

Glad it helped, Subhankar.

Great video. Thank you!

@Computergaga

6 жыл бұрын

You're welcome Griffin.

always coming back for this tutorial. thanks!

@Computergaga

3 жыл бұрын

No problem, Terence 😊

This is great, how could i do this across multiple sheets? Say I had similar data as you, but had 40 sheets each named after a year. How would I pull the top 10 from all sheets combined?

Thanks for this wonderful video

@Computergaga

6 жыл бұрын

Glad you like it Syed.

Hi Alan.. excellent.. another great tip. Thumbs up!

@Computergaga

5 жыл бұрын

You're welcome Wayne.

Very useful, thank you

@Computergaga

4 жыл бұрын

You're very welcome.

This is really helpful ! Thanks a lot :) But I am facing an issue that I have extract the same value for all top 10 somehow. I do have duplicated values in the "List" column, but do you think that is the case?

Thanks. All the best and keep up the good work.

@Computergaga

4 жыл бұрын

Thank you Akash.

Thank you, it is very helpful

@Computergaga

Жыл бұрын

Great! Happy to help.

Good knowledge sir thank u so much for this formula

@Computergaga

3 жыл бұрын

My pleasure, Sachin.

thank you so much for helping.

@Computergaga

5 жыл бұрын

You're welcome. Thank you Indrajit.

Nice job... thank you

@Computergaga

4 жыл бұрын

You're welcome. Thank you.

Cheers, that is so useful.

@Computergaga

3 жыл бұрын

You're welcome. Thank you, Paul-Maurice.

Brilliant Really helpful.

@Computergaga

3 жыл бұрын

You're very welcome, Pramod.

Thanks. It helped me

@Computergaga

Жыл бұрын

You're welcome! Thank you.

Thank you! this helped me so much (:

@Computergaga

4 жыл бұрын

Excellent! My pleasure.

Very Useful..

@Computergaga

4 жыл бұрын

Thank you.

EXCELLENT

@Computergaga

5 жыл бұрын

Thank you Ram.

I really enjoyed the video

@Computergaga

Жыл бұрын

Great! 😊

Thanx sir for prompt reply

@Computergaga

6 жыл бұрын

You're welcome.

This video is awesome and incredibly helpful. I'd just add that if you're referencing a column by the range (e.g., A1:A50) to make sure you're using absolute referencing (e.g., $A$1:$A$50) etc. when creating the "name" column. I was using a lot more data but ran into the problem of relative referencing and, had I not caught it, would have given me the completely wrong "names." Thanks again!

@Computergaga

4 жыл бұрын

Thank you Jem. Much appreciated.

@dineshltjd

4 жыл бұрын

@@Computergaga The only challenge with this approach is .. If 2 companies have same corresponding values then index match function will fail as this will show same company name twice in the list...

@sssagar4017

2 жыл бұрын

@@dineshltjd i also suffered same problem..

@sssagar4017

2 жыл бұрын

@@dineshltjd do you have solutions

awesome!

@Computergaga

4 жыл бұрын

Thank you Emma.

Question: how would the formula change, if searching for the top 10 values in multiple columns, instead of one column?

@Koreafut02

2 жыл бұрын

i need answer to this too lol

@techxtips

2 жыл бұрын

same question here...

Very nice video Sir

@Computergaga

5 жыл бұрын

Thank you Sada.

EVERY HELPFUL.

@Computergaga

Жыл бұрын

Thanks 🙂

How would I find values that are in the top ten in two columns, e.g. items that are in the top ten for both 3 months and 1-month lists?

Hi one more thing, I have a question, if they ask you: If you need the top 3, but it must appear as a pop up that said top 3, in the selected cell.

tnx

@Computergaga

4 жыл бұрын

My pleasure Reza.

Nice video

@Computergaga

6 жыл бұрын

Thank you Sandeep.

Great video. May I also check what if the TOP 3 in the E column are same sales figure. How am I going to match the different sales person name in the “D” column in your example. Please advice.

@Computergaga

5 жыл бұрын

I have a new video which uses Power Query for the top 10/top 3 - kzread.info/dash/bejne/p2SfyZKxecWwpdo.html

thank you sir. what if the data has same value? it kept refer to the first name with the same value on the table.

@norsalwati9138

4 жыл бұрын

I face the same problem. Do you find the solution?

thnk u

@Computergaga

4 жыл бұрын

You're welcome Ghous.

Thanx

@Computergaga

2 жыл бұрын

Thank you, Ali.

Thank you so much this video really helps me. But I have a problem when the value is the same few figure, they retrieve only one name. For example, if 3 people have the same marks, the data will capture the same name for all three. Please advise.

@Computergaga

4 жыл бұрын

I have a video on using COUNTIF to uniquely rank items with the same value - kzread.info/dash/bejne/g3qY1amAnLbTorg.html I also have this newer video on creating a top 10 rank and it also handles the problem you state - kzread.info/dash/bejne/pZ9n3JqeYtDcnso.html

This is a good video as far as it goes. My problem is I have duplicates within the top 10 and when I use the INDEX & MATCH function to show the names it will only show the first name of the duplicate sales. How do I get this formula to show the second name.

@Computergaga

5 жыл бұрын

Thank you Don. Check out this video on returning the top ten and whatever details you want - name and other columns if you have them and want - kzread.info/dash/bejne/p2SfyZKxecWwpdo.html A more complete solution than the formula because of the issue that you state.

Hi sir, if same amont in three raws, then how it works???

This is really helpful, so thanks. The only issue I've identified is where the value of 2 items in the sales column are equal, when this occurs the CompanyName index returns the first company name it finds twice. Any ideas of how to fix this?

@HoangNguyen-yl9eh

4 жыл бұрын

same question, is anyone find the answer?

@khushbooshanker

2 жыл бұрын

have you found the answer to this?

Suppose more than one same values occured means. How can we find

Dont you think so it was so elegant that i could barely move during your lecture..........

Great video! I had a question - I have duplicate values in the "total" range. When I pull the names over, it duplicates a single name rather than filling in all of the names that match that total. (IE: I have 3 $100's, rather than seeing three names, I see one name, three times.) Is there a way to fix this?

@Computergaga

6 жыл бұрын

I would use a formula in a new column next to Total column that uniquely ranks each value. I show this technique in this video - kzread.info/dash/bejne/g3qY1amAnLbTorg.html Then with this, we can then use a VLOOKUP to pull each name over using a technique like shown in this video - kzread.info/dash/bejne/g3WOla5vqMW0esY.html That should complete what you ask for.

@rsklen

2 жыл бұрын

I tried following this myself. And when I get to the Vlookup part. How do I get it to automatically apply in my top 10 list since you only showed how to find the last instance. I still need to show each instance just not pulling the first.

@erinyerkey2958

2 жыл бұрын

kzread.info/dash/bejne/l52gp5aqdqS6os4.html

This is really useful! What would be the formula for bottom 10 values?

@Computergaga

3 жыл бұрын

Thank you. Same but with SMALL instead of LARGE.

@footballratingsspreadsheet1372

Жыл бұрын

I did this formula with 'small' for bottom 5 of a list of values, but it didn't take into account negative values. It only returned the 5 smallest positive amounts. Can anyone advise here please? TIA

very helpfull.i only had a Problem with my table. If i have companies with the same Total, the index will refer to the first value correctly but repeat it in the nex same value. if my top 3 have the same total value, it will Show only the Name of the 1st one. Any idea on how to get around it? thanks a lot

@Computergaga

5 жыл бұрын

I have a video here on how to uniquely identify records with the same value - kzread.info/dash/bejne/g3qY1amAnLbTorg.html

The problem with the second part where it is indexed is if you have the same values in column 2, will it give 2 different lookup values in indexing column 1. It would be better if it step 1 could take multiple cells as one line.

@Computergaga

4 жыл бұрын

Please see this video Aaron - kzread.info/dash/bejne/pZ9n3JqeYtDcnso.html

Short sited video. What if there are multiple matching total values (which is often the case). Index match will return the same company name for each.

@Computergaga

Жыл бұрын

Thank you Sean. Not sure about 'often'. Depends on what you're ranking. If it is sales then it is rare to tie values especially when in the high hundreds like the video example. But yes it happens. And then are various approaches. This video shows one where the tied values share the same rank kzread.info/dash/bejne/pZ9n3JqeYtDcnso.html I have other videos, where the tied values are given distinct ranks, or other criteria is used to differentiate them.

What if you have 2 items with the same numerical value?

hey bro if sometimes the value is same of two names then it shows only the one name as same in second.... then what to do next in mobile

Hi Sir, I'm still confused how you entered sales in the formula =Large(sales. Where does this sales came from. Can u just help me out?

@motunrayoshodimu2728

10 ай бұрын

@Computergaga please how did you the sales name for col B?

Thank you for this tech. what happens if the data are duplicate? which one will be included in the list. let say the 9th record is duplicate

@Computergaga

2 жыл бұрын

You're welcome. In this example, it just includes the top 10 and does not account for duplicates. So if 9th is duplicated, then those results occupy 9th and 10th place, which is often ok and accurate. I do however, have another video showing ranking that handle tied values - kzread.info/dash/bejne/pZ9n3JqeYtDcnso.html

Is there a way to get the Index/Match function to work when there are duplicated numbers in the top 10 list?

@Computergaga

6 жыл бұрын

Hi Becky, INDEX and MATCH would just return the first value it finds without some advanced adaptations. I have this video on using COUNTIF to uniquely rank values to help create tables such as top 10 even when duplicates exist - kzread.info/dash/bejne/g3qY1amAnLbTorg.html

Why do I get a pop up message saying, we dound a problem with this formula, try clicking insert function on the formula tab to fix... I pressed =LARGE(L8:L57,{1;2;3}) What could be the problem, I want the top 3

Hi, I use above trick in my excel but it giving me the largest value into all 10 cells, please advise

@pramodbhatt6906

3 жыл бұрын

Hi Pl see the video again if u want top 10 values in case u have to select 10 rows and write the formula on formula bar only.

This is totaly Supper, But let me know whats the sales you include. is it total.

@Computergaga

6 ай бұрын

Thank you. Yes, the Total column.

What if two company names have the same value, how are you able to list them both ?

@Bollywoodbakarr

3 жыл бұрын

Did you got thid bcz same question arrise when I went to an operation.

It does not give me the correct list if 2 values are the same. The first name is then repeated. (Eg. If 2 sales values are 984, then both will return the first name rather than both the names in subsequent cells)... So what modification needs to be done in the formula to get the total list, even if the values are the same.

how do you highlight the column sales right after you typed "sales" in LARGE formula?

@Computergaga

6 жыл бұрын

Sales is a named range. You can create these by highlighting the range and typing the name you want to use "sales" in the Name Box (left of Formula Bar).You can then enter this into formulas, chart ranges etc.

I want know the first highest second highest and so on in a list...will this work for me

@pramodbhatt6906

3 жыл бұрын

Hi, If u want Top 2 pl close this formula by 1;2})

Hi can i do that but into page2 or as a report page ?

@Computergaga

6 жыл бұрын

Absolutely. You can write these formulas anywhere you want. Just refer to the cells on the other sheet. No problem.

I would like to to use a couple of conditional statements to control the data selection where a top 10 list is drawn from, ideally 2 drop-down boxes, so i can see top 10 by either volume (kg) or turnover (kg), also top 10 by either customer or product. My column headers will be Customer Name, Product, total volume bought (kg) and total turnover (£). Can I put conditional statements in with a Large function to do this and control it with 2 drop-downs to choose the type of data you see? It would enable 1 list to show 4 different sets of data - top 10 volume by product, top ten value by product, top ten volume by customer, top ten value by customer. Does that make sense? Can it be done?

@Computergaga

6 жыл бұрын

It can absolutely be done Al. Bit much to explain here though. You could use the LARGE function to create top 10 lists for all 4 situations. This could be on a hidden calculations tab. Then on the report sheet use either lookups or IF functions to show the right value dependent upon drop down list selection.

@alheeley

6 жыл бұрын

Thanks for the reply - sorted with 4 hidden tables and some radio buttons, and some nested IF's - rather crude solution but it works!

@Computergaga

6 жыл бұрын

Sounds awesome Al. You don't always need the Porsche to get from A to B.

What if the top ten includes 3 different values of $950? The company name e.g. "Giles Co." would be repeated 3 times, instead of showing the three company with that value... Any advice?

@Computergaga

5 жыл бұрын

This video demonstrates the COUNTIF function uniquely ranking values when they have the same value - kzread.info/dash/bejne/g3qY1amAnLbTorg.html

if have 2 value in column B, b4=b5, how to do. thanks bro

@Computergaga

6 жыл бұрын

I would use a helper column to uniquely rank them first as shown in this video - kzread.info/dash/bejne/g3qY1amAnLbTorg.html Then use a lookup formula to return them in order for a top 10. This is a technique I use in my sports league table course.

Wow

@Computergaga

3 жыл бұрын

👍

When you have duplicate values it chooses the first name it comes to and list the name twice. How do I get the formula to give me both names that are tied to the duplicate values.

@guilhermefilho

4 жыл бұрын

It seems that nobody knows, but it is the same question of me

@guilhermefilho

4 жыл бұрын

This guy solved the problem here: kzread.info/dash/bejne/pH94q4-klKXZmrw.html

I want to do the same but instead of returning the list in column E I want to mark all rows falling within the top range values (i.e. in your example, appearing in the list) in column C (say, with an 'X'). What is the function I need to put in column C? Thanks in advance!

@Computergaga

2 жыл бұрын

Something like this works, Nándor - =IF(B2>=LARGE($B$2:$B$13,10),"X","")

@nandorvass2550

2 жыл бұрын

@@Computergaga thank you very much

This works, but what if you are trying to pull a top 50 or top 100?

@Computergaga

4 жыл бұрын

This works, but there are other options also such as PivotTables and Power Query.

what if I have a third column with "orange" "apple" "banana" and i want to list the top sales only about "orange"? how it could be managed in your opinion?

@Computergaga

Жыл бұрын

There are a few approaches. One is to use COUNTIFS to create a unique ranking, video here kzread.info/dash/bejne/mWGAktevhsarn7A.html, and then use a lookup to return the items for each rank. Another option is to use FILTER to filter for that fruit and then SORT to order them.

what if the we have to find top 10 of row my list contains an item the below it name of suppliers that provide the item now how can i find the top 10 suppliers for particular item

@Computergaga

5 жыл бұрын

Sounds like the setup of the spreadsheet may complicate matters if the items are always on a row above the suppliers. Typically your best bet would be a PivotTable for this task. Item in the Filters area, Supplier in Rows and sorted largest to smallest in the values area.

how do you name the range? Intrinsically missing one of the important parts.

@Computergaga

5 жыл бұрын

Select the range you want to name, click in the Name box (top left next to the Formula bar) and type the name you want to use. Press Enter.

great video! Look, i have a similar column containing the %of the total sales for different products. i would like to create a rank where 5 would be if the percentage is on the top 20 values, 4 the 20-40, 3 the 50-60 and so on. any idea on how to do it? thanks a lot

@Computergaga

4 жыл бұрын

Once you work out if the values is in the top 20 values or not using a formula similar to the video. You can then use that result either in a lookup or a logical function like IF to provide the correct ranking.

@Gledii

4 жыл бұрын

@@Computergaga I just found out the formula Percentile.inc.with this one i can calculate if my percentage of net sales is in the upper 20% or 60%..etc..and then im using an if to give the ranking...a bit weird but it seems to work somehow..

@Computergaga

4 жыл бұрын

Excellent. Nice work Gledison.

Love it, however, what is happening is we have duplicated values in column b? Yes, it will take the first one... Anyway, I like it

@JP-zk6ef

5 жыл бұрын

what is the possible fix for this?

@user-hm6vy8oh2k

4 жыл бұрын

@@JP-zk6ef could anybody fix the similar value issue?

@JamieGarroch

4 жыл бұрын

I adressed this by appending a random number to the source data for decimal places 4 onwards to limit the chances of getting a match for two values which "look" to be the same. Use the formula =YOURVALUE+RAND()/1000

This works if you don't have multiple "LARGE" things that match (for me, at least). I freely admit, I'm not the best with Excel formulas, but this doesn't seem to work for me if I have an array where the numbers might repeat (like 50, 49, 49, 47, 30, 27, 27, 25). When I do this, it only returns the first Index/Match for the 49 and 27, wherein in reality those might be two different people. How would you resolve this with a formula? Example Table: Name Value Peter 50 Mikey 30 Jonni 49 Mary 47 Paul 30 Elvis 27 Harry 49 Priscilla 25 I am getting (for example, top 5 values) Peter 50 Jonni 49 Jonni 49 Mary 47 Mikey 30 When (for it to function like the Conditional formatting "Top 10") I should be getting: Peter 50 Jonni 49 Harry 49 Mary 47 Mikey 30 Paul 30 Notice how there are multiple values for the top 5 values? It's like golf, you can have more than one person in 2nd, 3rd, etc. place. You can end up with 10 people as "the top 5 finishers". I'm probably really messing up the explanation. But would you know the equations to match the Conditional Formatting's "Top 10"?

@Computergaga

5 жыл бұрын

Hi, i have a video on using COUNTIF to uniquely rank values - kzread.info/dash/bejne/g3qY1amAnLbTorg.html

What about if one of the values is repeated. what's the formula to pull the second name?

@Computergaga

6 жыл бұрын

Ah that is another story :) There are videos out on there on handling duplicates in this manner.

@karlayazuri8343

6 жыл бұрын

Thank you! any link you can share I tried searching and couldn't find any.

@Computergaga

6 жыл бұрын

Here is one Karla - kzread.info/dash/bejne/Y46KtZKyc5jOgco.html Demonstrates an array formula being used to return multiple items from a lookup.

I'm using this, but getting a strange result when values are tied (two+ names with the same value). Only one name shows in all three positions - instead of showing all three different names. Am I doing something wrong, or need to use a different formula?

@Computergaga

4 жыл бұрын

The COUNTIF function can be used to avoid the duplication - kzread.info/dash/bejne/g3qY1amAnLbTorg.html

Thank you! But I am getting some TIES here. Could you PLEASE let us know how to get rid of those?

@Computergaga

5 жыл бұрын

Thank you Marcelo. This video demonstrates a different way to uniquely rank values.

@Computergaga

5 жыл бұрын

Here - kzread.info/dash/bejne/g3qY1amAnLbTorg.html

@marceloribeirosimoes8959

5 жыл бұрын

@@Computergaga And I thank you for that. I am trying to rank about 500 players and some of them are appearing two or more times in the first 10 places list. I am not sure why. Is there a way to tie break the match function?

@Computergaga

5 жыл бұрын

The COUNTIF in the video is a way I have done it in the past. I have created many ranking tables where I rank based on different criteria. But if the criteria all the same, than I just use the COUNTIF to make the rankings unique.

@marceloribeirosimoes8959

5 жыл бұрын

@@Computergaga - But I don't know how to use COUNTIF with this =LARGE(REALOVR,{1;2;3;4;5;6;7;8;9;10}) thing... ...that's the problem.

what if you have two individuals with the same amount of sales?

@Computergaga

6 жыл бұрын

Please see the comment by Karla Yazuri yesterday

What if there are 2 names with the same sales?.

@Computergaga

Жыл бұрын

There a various approaches to this depending on what behaviour you want. This video shows a formula approach that merges the tied values to the same ranking kzread.info/dash/bejne/pZ9n3JqeYtDcnso.html

Sir what if there is someone with similar value of sale

@Computergaga

2 жыл бұрын

This formula will order them by values, so a similar value will be on another row, or if it's the 11th, not included.

@GOODVIBESTVPhil

2 жыл бұрын

@@Computergaga Thanks sir appreciate your reply

@Computergaga

2 жыл бұрын

@@GOODVIBESTVPhil no worries 👍

sir i have a problem in this formula i put large formula because i want top4 values in a range but in next stepp slaes not put in this formula plz solve my problem i have a number range what i do sir

@Computergaga

6 жыл бұрын

Sorry I do not understand your question. You have the top 4 values?

@shubhamharit6018

6 жыл бұрын

yes sir i wana to show top 4 values in a range n excel what i do sir

@Computergaga

6 жыл бұрын

That is what the video shows Shubham. I'm not sure where you want me to help.

What if there are multiple cells with same value?

@Computergaga

4 жыл бұрын

You can uniquely rank them with the COUNTIF function - kzread.info/dash/bejne/g3qY1amAnLbTorg.html I'm planning on producing another video with other techniques on this soon.

So, how to find a name with repeated values?

@Computergaga

5 жыл бұрын

We would ideally uniquely rank the values so that if there is a duplicate, both names appear. I have a video on unique ranking here - kzread.info/dash/bejne/g3qY1amAnLbTorg.html

How about if the Value is duplicated ?

@Computergaga

4 жыл бұрын

I have this video handling duplicated values by ranking with dynamic arrays - kzread.info/dash/bejne/pZ9n3JqeYtDcnso.html

@Computergaga

4 жыл бұрын

You could also handle it by counting how many of any index are duplicated with COUNTIFS and adding that number. I have a video here using COUNTIFS - kzread.info/dash/bejne/mWGAktevhsarn7A.html

Where is sales table in the file

@Computergaga

6 жыл бұрын

It is the table in columns A and B.

well error as usual. Formula parse error.

How do you handle two values that are the same because it doesn't give you the names of the two companies, just one twice.

@Computergaga

5 жыл бұрын

I have a video on uniquely ranking values here - kzread.info/dash/bejne/g3qY1amAnLbTorg.html Combine this to return both names.

SIR I NEED THE FOLLOWING RANK IN EXCEL HOW IT WILL BE POSSIBLE (AUTO AS RANK NORMAL FORMULA DO IT) ST MARKS RANK WE 50 1 ER 49 2 YT 48 3 PR 48 3 PP 47 4 LL 42 5 KK 40 6 MM 40 6 NB 39 7 THANKS IN ADVANCE.... WAIT FOR RESPONSE

How can write figure in Indian rupees like- 1,00,000/- one lakh, 10,00,000/- ten lakhs, 1,000/- thousands, 1,00,00,000/- one crore Sir how can I do this ...?

@Computergaga

6 жыл бұрын

This tutorial will show you how Naresh - chandoo.org/wp/2010/07/26/indian-currency-format-excel/

Lol this is useless and time consuming, why don't you just sort the list from Z to A with reference to column B

@Computergaga

6 жыл бұрын

Because we are looking at automating the process. If you had a report that people in the company view 24/7 even when you are asleep, or out the office. A formula would also show the top ten value without the need for someone to go and sort values.

@lubabalodyantyi256

6 жыл бұрын

Well explained. Now I understand.

Thanx

@Computergaga

2 жыл бұрын

No problem, Ali.