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 :)
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).
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. ruclips.net/video/fYn8WCm13AI/видео.html
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
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?
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
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: ruclips.net/video/E0uolp5xqHA/видео.html You have to pass an array of columns and an array of directions to the function.
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....
This video was extremely clear and helpful. Thank you very much!
Thank You Daniel
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! Your video was amazing and extremely helpful. I got it perfectly. Thank you for helping the community with such videos
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 ruclips.net/video/fYn8WCm13AI/видео.html
Very helpful thank you so much!
Easy to follow and Script worked without any altering, many thanks
Thank you ray...
Perfect explanation, thank you
Glad it was helpful!
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.
Appreciate your efforts .. thank you Sir.
Glad that this tutorial helped
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
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...
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
Thanks! Very helpful !
Glad that this video has helped you
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
I love you.exactly wht i was searching for
Happy that his helped you
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.
ruclips.net/video/fYn8WCm13AI/видео.html
@@TEKNISHA Thank you!
I'm doing the same thing for predicting Euro 2020, and was about to ask the same thing. Thank you!
Great ! Really Help full. one small request if i required only Top-10, can you please help me with the formula
Thanks a lot, very nice explaination
very thanks for the video, its help me a lot. Thanks Sir.
Glad that this helped you
Very Useful. Please guide how to automatically sort text in ascending order just like this.
Will upload soon
Very helpful.... Thanks so much
Brilliant!!! Thanks.
Glad that this helped out
You are the best 👏🏽👏🏽
Thank you Amina
thank youuuuu
You are very Welcome!!
Thank you!
You're welcome!
Thank you very much for this ....god bless you..
Glad that it was of use....
Truly awesome!!
Glad that this helped
Big thanks my dear
Thanks this was helpful. !!
nice to know Ketan...
Great video and great explanation ability!
very good bro thanks a lot it helped me too much
Great...
Thank you very much
Super lesson.
Glad that this helped you
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.
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 ruclips.net/video/fYn8WCm13AI/видео.html
Thank you
You're welcome
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..
Amazing!
Glad that this helped you
Your style is very nice
how can we do it? lets get to it!
love it, mucho gracias good sirrr
what to do when values are same in the column rank is showing same please provide soln.
Thanks so much! Exactly what I was looking for and explained clearly!
You are very Welcome!!
can you do this horizontally? thanks
Thank you!!!
I created my sheet as you did, but when I change the data on the left, it does not auto sort on the right.
Brilliant!!
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:
ruclips.net/video/fYn8WCm13AI/видео.html
Sir if the table is sorted with color so then how to do please help
Does anyone know if you can make images also move with the text? This is something that works with the sort normally
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!
How to sort running database while doing entries
hi, how do you fix if rank tied, it becomes n/a if tied
I tried it on my assignment in making payroll but it didn't work and came out with error.
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 you have equal numbers. then it won't work..
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
Different very new to me
Thanks to you.
Cheers!!
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: ruclips.net/video/E0uolp5xqHA/видео.html
You have to pass an array of columns and an array of directions to the function.
How to sort if there are 3 sales column against same customer?
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
SIR I WRITE 2020 IN EXCEL THEN IT CHANGE 20.20 AUTOMATICALLY THEN WHAT SHOULD I DO
NARENDRA SINGH
If they are equal?
Both will have the same rank
How to sort the Fx column?
What if we want to add new names and records?
Hello Behrang,
You will have to update the reference in the formula
Plse explain the same thing in form of row .You have taken example by taking row.So plse...
Is it possible sort data to another workbook
Yes it is possible, but both workbooks need to be open (source and destination)
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 so much sir for your reference reply.
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
Creativity
Thank you
Hello boss..we need a video which require sorting based on another data..
This tutorial is not useful..
Same value then
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
Great video, you made my day 😁
Thank you 😊🥰
Glad that this helped...
Thanks a lot
Thank you very much
Glad that this helped out
@@TEKNISHA Can I contact you if I facing some problem in excel
@@babaimandal1456 sure
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...
ty. nice! with a simple approach.