Excel Tutorial to Sort Data Automatically without using Macros
Tutorial to Sort Data Automatically in Excel including Duplicates (without Macros) using Rank and Index/Match Formula
🔶 Get Excel File at : bit.ly/3pj7G1H 🔶
Digital Products:
🔷 gumroad.com/teknisha 🔷
Recommended Gadgets/Products:
www.amazon.in/shop/teknisha
Check the below Playlists..
Excel Tutorials:
• TEKcel Series : Excel ...
Excel Chart Tutorials:
• TEKcel Chart Tutorials...
Excel Pivot Table Tutorials:
• Excel Complete Pivot T...
Excel Gsuite Tutorials:
• Gsuite Tutorials
Excel Tips & Tricks:
• TEKcel Tips : Excel Ti...
Powerpoint Tutorials:
• Tekpoint Series : Powe...
App Reviews: • Apps
Gadget Reviews:
• Gadget Reviews
Thanks for watching!!! 😊🙏
Please do Subscribe and Hit the Bell 🔔icon to support my efforts and to receive all my videos Notifications.
CLICK HERE TO SUBSCRIBE 👉 tinyurl.com/TeknishaSubscribe
Follow me on below to stay connected👇
👉FaceBook: / teknisha
👉Instagram: / teknisha18
Пікірлер: 155
Thank you very much! Your video was amazing and extremely helpful. I got it perfectly. Thank you for helping the community with such videos
This video was extremely clear and helpful. Thank you very much!
@TEKNISHA
4 жыл бұрын
Thank You Daniel
Really appreciate the knowledge given. Super helpful in trying to auto sort the rank during a tournament.
@TEKNISHA
3 жыл бұрын
Glad that this helped you. I myself used this first time for auto ranking in a tournament.. :-)
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.
Super Video, Anyone can easily understand this, @Thank u so much.
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).
@TEKNISHA
4 жыл бұрын
Glad, this video is of help to you lauracath1...
Easy to follow and Script worked without any altering, many thanks
@TEKNISHA
3 жыл бұрын
Thank you ray...
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
5 жыл бұрын
Thank you. Glad to be of help.....😊
@SibsFish
5 жыл бұрын
@@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.
@TEKNISHA
5 жыл бұрын
@@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.
@SibsFish
5 жыл бұрын
@@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 :)
@TEKNISHA
4 жыл бұрын
@@SibsFish Check my new video. This will show you how to sort based on multiple columns kzread.info/dash/bejne/mI2imrl8nZSZcaw.html
Thanks a lot, very nice explaination
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.
@TEKNISHA
2 жыл бұрын
Happy that this tutorial was of help
ty. nice! with a simple approach.
Thank you so much sir for your reference reply.
Great video and great explanation ability!
Great ! Really Help full. one small request if i required only Top-10, can you please help me with the formula
Very helpful.... Thanks so much
Does anyone know if you can make images also move with the text? This is something that works with the sort normally
Brilliant video, what an absolute legend
@TEKNISHA
3 жыл бұрын
Thank you very much John...I also have a video to sort based on multiple criteria... Check that one too...
Thank you!!!
Thank you very much
Appreciate your efforts .. thank you Sir.
@TEKNISHA
3 жыл бұрын
Glad that this tutorial helped
Great video, you made my day 😁 Thank you 😊🥰
@TEKNISHA
3 жыл бұрын
Glad that this helped...
Perfect explanation, thank you
@TEKNISHA
3 жыл бұрын
Glad it was helpful!
Big thanks my dear
Your style is very nice
Thanks! Very helpful !
@TEKNISHA
3 жыл бұрын
Glad that this video has helped you
Thank you very much for this ....god bless you..
@TEKNISHA
4 жыл бұрын
Glad that it was of use....
I love you.exactly wht i was searching for
@TEKNISHA
2 жыл бұрын
Happy that his helped you
Thanks a lot
very thanks for the video, its help me a lot. Thanks Sir.
@TEKNISHA
2 жыл бұрын
Glad that this helped you
Thanks this was helpful. !!
@TEKNISHA
4 жыл бұрын
nice to know Ketan...
Very helpful, thanks
@TEKNISHA
2 жыл бұрын
You are very welcome
Very Useful. Please guide how to automatically sort text in ascending order just like this.
@TEKNISHA
3 жыл бұрын
Will upload soon
Brilliant!!
very good bro thanks a lot it helped me too much
@TEKNISHA
3 жыл бұрын
Great...
Thank you!
@TEKNISHA
Жыл бұрын
You're welcome!
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!!! Thanks.
@TEKNISHA
3 жыл бұрын
Glad that this helped out
what to do when values are same in the column rank is showing same please provide soln.
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
@TEKNISHA
3 жыл бұрын
Hello Reza, Yes, you will have to update Reference in the formula.
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
@TEKNISHA
4 жыл бұрын
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.
@DartsGondel
4 жыл бұрын
@@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)
@TEKNISHA
4 жыл бұрын
@@DartsGondel please email me at bharath@teknisha.com
Truly awesome!!
@TEKNISHA
2 жыл бұрын
Glad that this helped
You are the best 👏🏽👏🏽
@TEKNISHA
4 жыл бұрын
Thank you Amina
can you do this horizontally? thanks
Sir if the table is sorted with color so then how to do please help
hi, how do you fix if rank tied, it becomes n/a if tied
Super lesson.
@TEKNISHA
3 жыл бұрын
Glad that this 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!
@TEKNISHA
3 жыл бұрын
Thanks joMat. Yes it is possible to sort based on multiple columns. I have made a video of it. Here is the link. kzread.info/dash/bejne/mI2imrl8nZSZcaw.html
@JoMat117
3 жыл бұрын
@@TEKNISHA Thank you!
@binupdhungana3889
3 жыл бұрын
I'm doing the same thing for predicting Euro 2020, and was about to ask the same thing. Thank you!
thank youuuuu
@TEKNISHA
3 ай бұрын
You are very Welcome!!
Thank you much
@TEKNISHA
3 жыл бұрын
Glad that this helped
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
@TEKNISHA
4 жыл бұрын
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?
@colinhuntley9729
4 жыл бұрын
@@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
@TEKNISHA
4 жыл бұрын
@@colinhuntley9729 Check my new video. This will show you how to sort based on multiple columns kzread.info/dash/bejne/mI2imrl8nZSZcaw.html
Different very new to me
How to sort running database while doing entries
Amazing!
@TEKNISHA
2 жыл бұрын
Glad that this 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?.
@TEKNISHA
2 жыл бұрын
This should work even for same ranks
@ztrkmtrk42
2 жыл бұрын
@@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, #?,#?
@TEKNISHA
2 жыл бұрын
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
@ztrkmtrk42
2 жыл бұрын
@@TEKNISHA thank u very much, i missed that part of video because of i tried instantly. very very useful video.
@TEKNISHA
2 жыл бұрын
Glad that it helped you
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
@eaglen5927
2 жыл бұрын
Actually here is the answer: kzread.info/dash/bejne/mI2imrl8nZSZcaw.html
Use a separate disconnected pc, then using macro's is no issue. Formulae are slow. License fees may be a problem.
Thanks so much! Exactly what I was looking for and explained clearly!
@TEKNISHA
3 ай бұрын
You are very Welcome!!
SIR I WRITE 2020 IN EXCEL THEN IT CHANGE 20.20 AUTOMATICALLY THEN WHAT SHOULD I DO NARENDRA SINGH
Thank you
@TEKNISHA
Жыл бұрын
You're welcome
Thanks to you.
@TEKNISHA
4 жыл бұрын
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
@TEKNISHA
4 жыл бұрын
Hello steve . Yes I can. Please give me your mail Id.
@StevieC82
4 жыл бұрын
@@TEKNISHA great thanks. Stephenclark82@gmail.com
@ryanbailey273
Жыл бұрын
This tutorial is good for auto sorting with multiple columns: kzread.info/dash/bejne/d2Sp0c6pZdvXeKQ.html You have to pass an array of columns and an array of directions to the function.
Thank you so much sir! I have a little problem. It is error if there's a duplicate.
@TEKNISHA
3 жыл бұрын
Hey. This method should account for duplicates. Kindly check if you have followed the tutorial exactly..
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?
@TEKNISHA
4 жыл бұрын
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
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?
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?
@TEKNISHA
3 жыл бұрын
Please see the video. I have shown a workaround in it.
How to sort if there are 3 sales column against same customer?
what if you have equal numbers. then it won't work..
Is it possible to make it so it automatically changes in the same table of raw data?
@TEKNISHA
3 жыл бұрын
Hello Oriol, It is not possible using only formulas. If you use macros , then you can change it in the raw data
@orioltorrell
3 жыл бұрын
@@TEKNISHA ah, I see. Thank you!
Is it possible sort data to another workbook
@TEKNISHA
4 жыл бұрын
Yes it is possible, but both workbooks need to be open (source and destination)
What if we want to add new names and records?
@TEKNISHA
3 жыл бұрын
Hello Behrang, You will have to update the reference in the formula
How do you do this with dates in excel online
@TEKNISHA
2 жыл бұрын
Dates are like any other numbers, this method should work in excel online too..
@amberarambula2820
2 жыл бұрын
@@TEKNISHA thank you, no matter what I do it is not working for me
@TEKNISHA
2 жыл бұрын
@@amberarambula2820 Does the method work if you use number instead of dates
If they are equal?
@TEKNISHA
2 жыл бұрын
Both will have the same rank
Plse explain the same thing in form of row .You have taken example by taking row.So plse...
❤❤❤❤
@TEKNISHA
2 жыл бұрын
Glad that you liked it
Same value then
this is very complicated 🤯
@TEKNISHA
3 жыл бұрын
Hello Ishu. This method may take a little time to setup, but gives you great results....
Creativity
@TEKNISHA
2 жыл бұрын
Thank you
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....
Hello boss..we need a video which require sorting based on another data.. This tutorial is not useful..
I Like it. Thanks. SUB****BED
@TEKNISHA
2 жыл бұрын
Glad that you liked it. Thank you for subscribing
Too long winded, waste of time
Very helpful, Thanks
@TEKNISHA
4 жыл бұрын
Glad this video was of help
Thank you very much
@TEKNISHA
3 жыл бұрын
Glad that this helped out
@babaimandal1456
3 жыл бұрын
@@TEKNISHA Can I contact you if I facing some problem in excel
@TEKNISHA
3 жыл бұрын
@@babaimandal1456 sure