I have been trailing the internet for days and days to find this information. Everything pointed towards macros, which i have no knowledge in ! Don't let the views bother you. At the very least your time has helped me and those i have done the formulae for. Thank you @TEKNISHA
@@TEKNISHA I do have a question. What about if i need to base it off of 2 columns ? So "sales" would be the primary the formulae is based on, then a 2nd column to be the secondary ? So if "sales" are tied then it would rely on the 2nd column ? I hope that makes sense.
@@SibsFishyou can do this using a helper rank column. The helper rank col will be( 1* the rank formula based on primary col + 0.1 * rank formula based on secondary column) . so let's say 2 values are tied for 3rd place in primary column, then based on secondary column their helper rank value will be 3.1 & 3.2 respectively. Then you can calculate the actual rank based on the helper rank col values.
@@TEKNISHA I am a beginner and that completely went over my head. I will have to google that, as my table is based on something like a league table. Thanks for the reply none the less :)
Thank you. I have been trying to do something similar with SUM / INDEX / MATCH / LARGE functions, as I was advised in the first video I tried. But I, too, have the ranking problem where, if there are two identical values, Excel deletes one of the two items whose values are identical. I will rethink the whole mini-project from the start using your RANK and COUNTIF idea. Here goes... SUCCESS! SUCCESS! SUCCESS! SUCCESS! SUCCESS! SUCCESS! SUCCESS! SUCCESS! It took me hours to get to the point where I chose to use a different method - your method. It took me about 90 minutes to copy you, as, after many failures, I decided to copy EXACTLY your sheet's layout. So when you said Column A Row 1, I did exactly the same, without trying to adjust the cell references. Now, after watching your video about 15 times, stopping and rewiding a few seconds every time you did something, I have what I want, including the COUNTIF function, which has removed the errors based on duplicates. I have 15 rows of data, not six, but I have managed to adject the formulas to include all the data. Now I can do a lesson for my English students in which we can rank the importance of items for an emergency bag for disaster preparation. THANK YOU FROM JAPAN! I have a policy of not subscribing to a channel after watching just one video. I have subscribed to yours and visited your website.
I am going to try to use your COUNTIF advise with my original sheet, too. Not today, as I have no more time, but another day. Then I will tell you, in this thread, if I had success. I will try to use your advise with these formulae... =LARGE($B$2:$B$16, J2) =INDEX($A$2:$A$16, MATCH(LARGE($B$2:$B$16, J2), $B$2:$B$16, 0))
This was very helpful. However, I have a question. In my table, when people have the same amount of points, they should have the same rank. In your example, Liam is rank 4 and Olivia is rank 5 although they have the same amount of points. Is it possible to adjust this so that Liam and Olivia get rank 4 but Noah still gets rank 6 (as there is no rank 5)? Thank you very much
Darts Gondel We can do that by adding one more column. In Col E of my example add Rank2 Col and calculate it using the regular RANK formula (same as I show in the first half of my video) so when Liam and Olivia have same sales number, Liam would have Rank as 4 & Rank 2 as 4 and Olivia would have Rank as 5 & Rank 2 as 4. In the sorted data now add another Col called rank 2. As you get the data for Rep and Sales in the sorted data table using index match, you can now get the rank 2 data too. So S.No.4 (Liam ) will have Rank 2 as 4 and S.No 5 will also have Rank 2 as 4. Now you can hide the S.No column.
@@TEKNISHA Hello Teknisha, thanks for your reply! Is there an email I could reach you at? I would like to show you what I have built and ask if you could look at one more thing that happens (when like 3 people tie the score for last in the ranking, and that ranking gives points, then only one of those does get the points)
Is it possible to make another number decide if two of the numbers are the same. For example, I have a table for a sport where goal-difference matters. Is it possible to make it so that the goal-difference decides which one gets higher rank if the points match? Great tutorial!
Thanks joMat. Yes it is possible to sort based on multiple columns. I have made a video of it. Here is the link. th-cam.com/video/fYn8WCm13AI/w-d-xo.html
great explanation but in my data numbers are so small, so there is lots of people gets same rank. in a result sorted data shows just one of them, because sorted list is based on given numbers like 1,2,3,4,5. how can i solve the problem of same rank problem?.
@@TEKNISHA first part works fine. But because of same ranks, sorted data section writes only one of them and skip blank ranks. for example from your list; emma, noah and liam takes rank 4 because of their sales are equal. because of this, in your sorted data section will sort first 3 people perfectly but excel picks just one of the 4th ranks. 5th and 6th ranks are remain blank. Like; william, Olivia, ava, Emma, #?,#?
Great video Teknisha! Thank you for helping me out! I was getting desperate as the only tutorials I found were too easy (using the SORT button, duh!) and the ones using Macros (not an option for my specific case).
Nice turorial very solutive however i'd like to ask what if i want to add another data ? let say i have Billy with sales 27.854 as far as i try i have to change the ref in rank formula
good day, teknisha! ich could not agree with anymore! your video was easy to understand and i tried your example myself, with the same results - perfect! if i may ask you one question, it would be this: i have tow colums of words (with translation), english to german: for instance clear -> klar, tight -> eng, etc. is it possible to automatically update each column so that the colums are in the correct order? up to now i have only being able to sort automatically in one direction? it would be excellent if you could give some advice! when it is not clear what i mean, please let me know. kind regards from berlin, germany. colin
Hello Colin. I am happy that this tutorial was of help to you. I dont think I understand your question completely. You want to sort based on words (alphabetically) in the English language column?
@@TEKNISHA hallo teknisha, thank you very much for your reply. i apolgize for my rather shaky explanation. please allow me to try again: 3 colums of raw data: english, german, company. these are expanding columns and data is entered unsorted in column english and german. there are also many duplicates, which are needed. for example: german english company selbstbewusst self-assured Neonyt neugierig curious Neonyt überzeugend convincing Neonyt kraftvoll energetic Neonyt kreativ creative Neonyt these colums need to be sorted, 1) in german with the english translation and 2) english with german translation, for example: (german english) kraftvoll energetic kreativ creative neugierig curious selbstbewusst self-assured überzeugend convincing and (english german) convincing überzeugend creative kreativ curious neugierig energetic kraftvoll self-assured selbstbewusst as i said, the colums are very long and expand constantly. also there are many duplictes which i need. please excuse the formatting. if it would held i could send you an excel file. many many thanks in advance. colin
Hi friend. How to have sort levels by using formulas? for example I need to sort column C values, first based on column A then sort them based on column B
Very helpful, can you also assist with sorting a table with multiple columns based only on the values of one of the columns? if its easier I can send you the file? Thanks
This tutorial is good for auto sorting with multiple columns: th-cam.com/video/E0uolp5xqHA/w-d-xo.html You have to pass an array of columns and an array of directions to the function.
Hey TEKNISHA, I have a question, if i have to work with Three columns of Alphabets, will the Index formula help me sort it according to ascending order?
Hi prajwal. Index formula is to associate the rank number and the respective details (like name etc..). The rank function will not be able to sort alphabetically. To rank a column alphabetically you can use countif formula
Noice! WHat if there are two people with same sales amount the rank function will return same rank for both of them but index will pick only first occurance? how to overcome this?
why do you copy and paste stuff? excell is all about dragging, otherwise why even use F4? I'm following this and had to add a few more $ that you don't miss cause you're coping and pasting, but then again, you don't really need any of the $'s cause you are coping and pasting....
Thank you sir. This video has been a game changer for me.
I have been trailing the internet for days and days to find this information. Everything pointed towards macros, which i have no knowledge in ! Don't let the views bother you. At the very least your time has helped me and those i have done the formulae for. Thank you @TEKNISHA
Thank you. Glad to be of help.....😊
@@TEKNISHA I do have a question. What about if i need to base it off of 2 columns ? So "sales" would be the primary the formulae is based on, then a 2nd column to be the secondary ? So if "sales" are tied then it would rely on the 2nd column ? I hope that makes sense.
@@SibsFishyou can do this using a helper rank column. The helper rank col will be( 1* the rank formula based on primary col + 0.1 * rank formula based on secondary column) . so let's say 2 values are tied for 3rd place in primary column, then based on secondary column their helper rank value will be 3.1 & 3.2 respectively. Then you can calculate the actual rank based on the helper rank col values.
@@TEKNISHA I am a beginner and that completely went over my head. I will have to google that, as my table is based on something like a league table. Thanks for the reply none the less :)
@@SibsFish Check my new video. This will show you how to sort based on multiple columns th-cam.com/video/fYn8WCm13AI/w-d-xo.html
Thank you.
I have been trying to do something similar with SUM / INDEX / MATCH / LARGE functions, as I was advised in the first video I tried.
But I, too, have the ranking problem where, if there are two identical values, Excel deletes one of the two items whose values are identical.
I will rethink the whole mini-project from the start using your RANK and COUNTIF idea.
Here goes...
SUCCESS! SUCCESS! SUCCESS! SUCCESS!
SUCCESS! SUCCESS! SUCCESS! SUCCESS!
It took me hours to get to the point where I chose to use a different method - your method.
It took me about 90 minutes to copy you, as, after many failures, I decided to copy EXACTLY your sheet's layout. So when you said Column A Row 1, I did exactly the same, without trying to adjust the cell references.
Now, after watching your video about 15 times, stopping and rewiding a few seconds every time you did something, I have what I want, including the COUNTIF function, which has removed the errors based on duplicates.
I have 15 rows of data, not six, but I have managed to adject the formulas to include all the data.
Now I can do a lesson for my English students in which we can rank the importance of items for an emergency bag for disaster preparation.
THANK YOU FROM JAPAN!
I have a policy of not subscribing to a channel after watching just one video.
I have subscribed to yours and visited your website.
I am going to try to use your COUNTIF advise with my original sheet, too.
Not today, as I have no more time, but another day.
Then I will tell you, in this thread, if I had success.
I will try to use your advise with these formulae...
=LARGE($B$2:$B$16, J2)
=INDEX($A$2:$A$16, MATCH(LARGE($B$2:$B$16, J2), $B$2:$B$16, 0))
This video was extremely clear and helpful. Thank you very much!
Thank You Daniel
Thank you so much. Can we do this at the different sheet? Raw data and shorted data at the different sheet.
Really appreciate the knowledge given. Super helpful in trying to auto sort the rank during a tournament.
Glad that this helped you. I myself used this first time for auto ranking in a tournament.. :-)
Super Video,
Anyone can easily understand this, @Thank u so much.
Thank you very much Sir.
I was so sad before watching your video. But its solved my problem in sorting duplicate values.
God bless you.
Thank you very much! Your video was amazing and extremely helpful. I got it perfectly. Thank you for helping the community with such videos
Easy to follow and Script worked without any altering, many thanks
Thank you ray...
This was very helpful. However, I have a question. In my table, when people have the same amount of points, they should have the same rank. In your example, Liam is rank 4 and Olivia is rank 5 although they have the same amount of points. Is it possible to adjust this so that Liam and Olivia get rank 4 but Noah still gets rank 6 (as there is no rank 5)? Thank you very much
Darts Gondel We can do that by adding one more column. In Col E of my example add Rank2 Col and calculate it using the regular RANK formula (same as I show in the first half of my video) so when Liam and Olivia have same sales number, Liam would have Rank as 4 & Rank 2 as 4 and Olivia would have Rank as 5 & Rank 2 as 4. In the sorted data now add another Col called rank 2. As you get the data for Rep and Sales in the sorted data table using index match, you can now get the rank 2 data too. So S.No.4 (Liam ) will have Rank 2 as 4 and S.No 5 will also have Rank 2 as 4. Now you can hide the S.No column.
@@TEKNISHA Hello Teknisha, thanks for your reply! Is there an email I could reach you at? I would like to show you what I have built and ask if you could look at one more thing that happens (when like 3 people tie the score for last in the ranking, and that ranking gives points, then only one of those does get the points)
@@DartsGondel please email me at bharath@teknisha.com
Very helpful thank you so much!
Is it possible to make another number decide if two of the numbers are the same. For example, I have a table for a sport where goal-difference matters. Is it possible to make it so that the goal-difference decides which one gets higher rank if the points match? Great tutorial!
Thanks joMat. Yes it is possible to sort based on multiple columns. I have made a video of it.
Here is the link.
th-cam.com/video/fYn8WCm13AI/w-d-xo.html
@@TEKNISHA Thank you!
I'm doing the same thing for predicting Euro 2020, and was about to ask the same thing. Thank you!
Perfect explanation, thank you
Glad it was helpful!
Appreciate your efforts .. thank you Sir.
Glad that this tutorial helped
Brilliant video, what an absolute legend
Thank you very much John...I also have a video to sort based on multiple criteria... Check that one too...
Great video, you made my day 😁
Thank you 😊🥰
Glad that this helped...
Thank you so much sir for your reference reply.
I love you.exactly wht i was searching for
Happy that his helped you
Does anyone know if you can make images also move with the text? This is something that works with the sort normally
Great ! Really Help full. one small request if i required only Top-10, can you please help me with the formula
very thanks for the video, its help me a lot. Thanks Sir.
Glad that this helped you
Thanks! Very helpful !
Glad that this video has helped you
great explanation but in my data numbers are so small, so there is lots of people gets same rank. in a result sorted data shows just one of them, because sorted list is based on given numbers like 1,2,3,4,5. how can i solve the problem of same rank problem?.
This should work even for same ranks
@@TEKNISHA first part works fine. But because of same ranks, sorted data section writes only one of them and skip blank ranks. for example from your list; emma, noah and liam takes rank 4 because of their sales are equal. because of this, in your sorted data section will sort first 3 people perfectly but excel picks just one of the 4th ranks. 5th and 6th ranks are remain blank. Like; william, Olivia, ava, Emma, #?,#?
Hey. Did you see the complete video? After 5.30 min in the video, I show how to deal with duplicate values, so that both are displayed
@@TEKNISHA thank u very much, i missed that part of video because of i tried instantly. very very useful video.
Glad that it helped you
Very helpful, Thanks
Glad this video was of help
You are the best 👏🏽👏🏽
Thank you Amina
Great video Teknisha! Thank you for helping me out! I was getting desperate as the only tutorials I found were too easy (using the SORT button, duh!) and the ones using Macros (not an option for my specific case).
Glad, this video is of help to you lauracath1...
what to do when values are same in the column rank is showing same please provide soln.
can you do this horizontally? thanks
very good bro thanks a lot it helped me too much
Great...
ty. nice! with a simple approach.
I created my sheet as you did, but when I change the data on the left, it does not auto sort on the right.
Very helpful.... Thanks so much
Nice turorial very solutive however i'd like to ask what if i want to add another data ? let say i have Billy with sales 27.854
as far as i try i have to change the ref in rank formula
Hello Reza, Yes, you will have to update Reference in the formula.
This is amazing. I was able to solve one of my excel
problems with this video. I express my gratitude for uploading this amazingly helpful video.
Happy that this tutorial was of help
Sir if the table is sorted with color so then how to do please help
how can we do it? lets get to it!
love it, mucho gracias good sirrr
How to sort running database while doing entries
Is it possible to make it so it automatically changes in the same table of raw data?
Hello Oriol,
It is not possible using only formulas. If you use macros , then you can change it in the raw data
@@TEKNISHA ah, I see. Thank you!
hi, how do you fix if rank tied, it becomes n/a if tied
Truly awesome!!
Glad that this helped
Super lesson.
Glad that this helped you
Thanks a lot, very nice explaination
good day, teknisha! ich could not agree with anymore! your video was easy to understand and i tried your example myself, with the same results - perfect!
if i may ask you one question, it would be this: i have tow colums of words (with translation), english to german: for instance clear -> klar, tight -> eng, etc. is it possible to automatically update each column so that the colums are in the correct order? up to now i have only being able to sort automatically in one direction?
it would be excellent if you could give some advice! when it is not clear what i mean, please let me know.
kind regards from berlin, germany. colin
Hello Colin. I am happy that this tutorial was of help to you. I dont think I understand your question completely. You want to sort based on words (alphabetically) in the English language column?
@@TEKNISHA hallo teknisha, thank you very much for your reply. i apolgize for my rather shaky explanation. please allow me to try again: 3 colums of raw data: english, german, company. these are expanding columns and data is entered unsorted in column english and german. there are also many duplicates, which are needed. for example:
german english company
selbstbewusst self-assured Neonyt
neugierig curious Neonyt
überzeugend convincing Neonyt
kraftvoll energetic Neonyt
kreativ creative Neonyt
these colums need to be sorted, 1) in german with the english translation and 2) english with german translation,
for example:
(german english)
kraftvoll energetic
kreativ creative
neugierig curious
selbstbewusst self-assured
überzeugend convincing
and
(english german)
convincing überzeugend
creative kreativ
curious neugierig
energetic kraftvoll
self-assured selbstbewusst
as i said, the colums are very long and expand constantly. also there are many duplictes which i need.
please excuse the formatting. if it would held i could send you an excel file.
many many thanks in advance.
colin
@@colinhuntley9729 Check my new video. This will show you how to sort based on multiple columns th-cam.com/video/fYn8WCm13AI/w-d-xo.html
Brilliant!!! Thanks.
Glad that this helped out
Hi friend. How to have sort levels by using formulas? for example I need to sort column C values, first based on column A then sort them based on column B
Actually here is the answer:
th-cam.com/video/fYn8WCm13AI/w-d-xo.html
Very Useful. Please guide how to automatically sort text in ascending order just like this.
Will upload soon
Your style is very nice
Great video and great explanation ability!
GOOD KNOWLEDGE SIR
Thanks this was helpful. !!
nice to know Ketan...
Thank you so much sir! I have a little problem. It is error if there's a duplicate.
Hey. This method should account for duplicates. Kindly check if you have followed the tutorial exactly..
Thanks so much! Exactly what I was looking for and explained clearly!
You are very Welcome!!
How do you do this with dates in excel online
Dates are like any other numbers, this method should work in excel online too..
@@TEKNISHA thank you, no matter what I do it is not working for me
@@amberarambula2820 Does the method work if you use number instead of dates
Big thanks my dear
Amazing!
Glad that this helped you
Thank you very much for this ....god bless you..
Glad that it was of use....
Brilliant!!
If they are equal?
Both will have the same rank
what if you have equal numbers. then it won't work..
Thank you!
You're welcome!
Thank you!!!
Very helpful, can you also assist with sorting a table with multiple columns based only on the values of one of the columns? if its easier I can send you the file? Thanks
Hello steve . Yes I can. Please give me your mail Id.
@@TEKNISHA great thanks. Stephenclark82@gmail.com
This tutorial is good for auto sorting with multiple columns: th-cam.com/video/E0uolp5xqHA/w-d-xo.html
You have to pass an array of columns and an array of directions to the function.
Is it possible sort data to another workbook
Yes it is possible, but both workbooks need to be open (source and destination)
How to sort if there are 3 sales column against same customer?
I tried it on my assignment in making payroll but it didn't work and came out with error.
How to sort the Fx column?
Thank you very much
Glad that this helped out
@@TEKNISHA Can I contact you if I facing some problem in excel
@@babaimandal1456 sure
Use a separate disconnected pc, then using macro's is no issue. Formulae are slow. License fees may be a problem.
Hey TEKNISHA, I have a question, if i have to work with Three columns of Alphabets, will the Index formula help me sort it according to ascending order?
Hi prajwal. Index formula is to associate the rank number and the respective details (like name etc..). The rank function will not be able to sort alphabetically. To rank a column alphabetically you can use countif formula
Thank you
You're welcome
Noice! WHat if there are two people with same sales amount the rank function will return same rank for both of them but index will pick only first occurance? how to overcome this?
Please see the video. I have shown a workaround in it.
What if we want to add new names and records?
Hello Behrang,
You will have to update the reference in the formula
SIR I WRITE 2020 IN EXCEL THEN IT CHANGE 20.20 AUTOMATICALLY THEN WHAT SHOULD I DO
NARENDRA SINGH
Different very new to me
Thanks to you.
Cheers!!
Plse explain the same thing in form of row .You have taken example by taking row.So plse...
Same value then
this is very complicated 🤯
Hello Ishu. This method may take a little time to setup, but gives you great results....
❤❤❤❤
Glad that you liked it
why do you copy and paste stuff? excell is all about dragging, otherwise why even use F4? I'm following this and had to add a few more $ that you don't miss cause you're coping and pasting, but then again, you don't really need any of the $'s cause you are coping and pasting....
Creativity
Thank you
Hello boss..we need a video which require sorting based on another data..
This tutorial is not useful..
I Like it. Thanks. SUB****BED
Glad that you liked it. Thank you for subscribing
Too long winded, waste of time
Very helpful, thanks
You are very welcome
Thank you much
Glad that this helped
Thank you very much
Thanks a lot