Excel Tutorial to Sort Data Automatically without using Macros

[@SibsFish]

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]

Thank you. Glad to be of help.....😊

[@SibsFish]

@@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]

@@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]

@@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]

@@SibsFish Check my new video. This will show you how to sort based on multiple columns kzbin.info/www/bejne/nIrRaYp5opZmd6s

[@danieltiburcio8871]

This video was extremely clear and helpful. Thank you very much!

[@TEKNISHA]

Thank You Daniel

[@staninjapan07]

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.

[@staninjapan07]

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))

[@mohdsyafiqsharani6474]

Really appreciate the knowledge given. Super helpful in trying to auto sort the rank during a tournament.

[@TEKNISHA]

Glad that this helped you. I myself used this first time for auto ranking in a tournament.. :-)

[@fairboy5349]

Thank you sir. This video has been a game changer for me.

[@waxedtaters]

Very helpful thank you so much!

[@thakuranujsingh4419]

Super Video, Anyone can easily understand this, @Thank u so much.

[@Luisyclaudia]

Thank you very much! Your video was amazing and extremely helpful. I got it perfectly. Thank you for helping the community with such videos

[@DartsGondel]

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]

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]

@@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]

@@DartsGondel please email me at [e-mail]

[@SaifAHadi]

Appreciate your efforts .. thank you Sir.

[@TEKNISHA]

Glad that this tutorial helped

[@rayharrison695]

Easy to follow and Script worked without any altering, many thanks

[@TEKNISHA]

Thank you ray...

[@timvdmeer4122]

Perfect explanation, thank you

[@TEKNISHA]

Glad it was helpful!

[@Ketot6041]

Thank you so much. Can we do this at the different sheet? Raw data and shorted data at the different sheet.

[@johnrking1987]

Brilliant video, what an absolute legend

[@TEKNISHA]

Thank you very much John...I also have a video to sort based on multiple criteria... Check that one too...

[@khansvirtualdiary]

Thank you so much sir for your reference reply.

[@Stop_War_Fight_with_Pollution]

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.

[@fihamahamed1276]

I love you.exactly wht i was searching for

[@TEKNISHA]

Happy that his helped you

[@Ponurak149]

Thanks! Very helpful !

[@TEKNISHA]

Glad that this video has helped you

[@Mohideenjailanizah]

Very helpful, Thanks

[@TEKNISHA]

Glad this video was of help

[@rjchua2008]

ty. nice! with a simple approach.

[@naufalb.prasetyo9360]

Great video, you made my day 😁 Thank you 😊🥰

[@TEKNISHA]

Glad that this helped...

[@cbcoksa1608]

very thanks for the video, its help me a lot. Thanks Sir.

[@TEKNISHA]

Glad that this helped you

[@JoMat117]

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]

Thanks joMat. Yes it is possible to sort based on multiple columns. I have made a video of it. Here is the link. kzbin.info/www/bejne/nIrRaYp5opZmd6s

[@JoMat117]

@@TEKNISHA Thank you!

[@binupdhungana3889]

I'm doing the same thing for predicting Euro 2020, and was about to ask the same thing. Thank you!

[@ahmedmoustapha7941]

Very helpful.... Thanks so much

[@uqbaabuhusaina5939]

very good bro thanks a lot it helped me too much

[@TEKNISHA]

Great...

[@aminachibwana5663]

You are the best 👏🏽👏🏽

[@TEKNISHA]

Thank you Amina

[@barrygorst5951]

Brilliant!!! Thanks.

[@TEKNISHA]

Glad that this helped out

[@RGR2155]

Truly awesome!!

[@TEKNISHA]

Glad that this helped

[@simonetommasinipodcast]

Great video and great explanation ability!

[@assemqasim7258]

Thanks a lot, very nice explaination

[@JAMILAKKAS]

Your style is very nice

[@ijnmpi]

Thanks this was helpful. !!

[@TEKNISHA]

nice to know Ketan...

[@audadr]

Super lesson.

[@TEKNISHA]

Glad that this helped you

[@lauracath1]

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]

Glad, this video is of help to you lauracath1...

[@mayechisavali1919]

GOOD KNOWLEDGE SIR

[@ztrkmtrk42]

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]

This should work even for same ranks

[@ztrkmtrk42]

@@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]

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]

@@TEKNISHA thank u very much, i missed that part of video because of i tried instantly. very very useful video.

[@TEKNISHA]

Glad that it helped you

[@Giridhar.M]

Great ! Really Help full. one small request if i required only Top-10, can you please help me with the formula

[@tamilwoodsviews2089]

Amazing!

[@TEKNISHA]

Glad that this helped you

[@josebaztan77]

Thank you!

[@TEKNISHA]

You're welcome!

[@RaceBeacon]

Does anyone know if you can make images also move with the text? This is something that works with the sort normally

[@vivekmusafir4492]

Thank you very much for this ....god bless you..

[@TEKNISHA]

Glad that it was of use....

[@closeup3749]

Big thanks my dear

[@grandpasaysrelax175]

Brilliant!!

[@honesttruth532]

Thank you!!!

[@skumargee2000]

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]

Happy that this tutorial was of help

[@orioltorrell]

Is it possible to make it so it automatically changes in the same table of raw data?

[@TEKNISHA]

Hello Oriol, It is not possible using only formulas. If you use macros , then you can change it in the raw data

[@orioltorrell]

@@TEKNISHA ah, I see. Thank you!

[@JayDominicSumalinog]

can you do this horizontally? thanks

[@colinhuntley9729]

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]

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]

@@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]

@@colinhuntley9729 Check my new video. This will show you how to sort based on multiple columns kzbin.info/www/bejne/nIrRaYp5opZmd6s

[@rezanugraha6537]

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]

Hello Reza, Yes, you will have to update Reference in the formula.

[@dr.shrikanthadole7691]

Very Useful. Please guide how to automatically sort text in ascending order just like this.

[@TEKNISHA]

Will upload soon

[@dustinworthy2750]

I created my sheet as you did, but when I change the data on the left, it does not auto sort on the right.

[@babaimandal1456]

Thank you very much

[@TEKNISHA]

Glad that this helped out

[@babaimandal1456]

@@TEKNISHA Can I contact you if I facing some problem in excel

[@TEKNISHA]

@@babaimandal1456 sure

[@jwink7795]

how can we do it? lets get to it! love it, mucho gracias good sirrr

[@atenterprises3436]

what to do when values are same in the column rank is showing same please provide soln.

[@vernondavids8312]

Thanks so much! Exactly what I was looking for and explained clearly!

[@TEKNISHA]

You are very Welcome!!

[@zainulabidinhussainkhan495]

Sir if the table is sorted with color so then how to do please help

[@ManjulabeniSurti-mt1cz]

Thank you

[@TEKNISHA]

You're welcome

[@jong-jongspeaks4224]

Thank you so much sir! I have a little problem. It is error if there's a duplicate.

[@TEKNISHA]

Hey. This method should account for duplicates. Kindly check if you have followed the tutorial exactly..

[@eaglen5927]

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]

Actually here is the answer: kzbin.info/www/bejne/nIrRaYp5opZmd6s

[@krisdelacruz1308]

hi, how do you fix if rank tied, it becomes n/a if tied

[@amberarambula2820]

How do you do this with dates in excel online

[@TEKNISHA]

Dates are like any other numbers, this method should work in excel online too..

[@amberarambula2820]

@@TEKNISHA thank you, no matter what I do it is not working for me

[@TEKNISHA]

@@amberarambula2820 Does the method work if you use number instead of dates

[@sunilshetye1618]

How to sort running database while doing entries

[@prajwalbraj4704]

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]

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

[@StevieC82]

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]

Hello steve . Yes I can. Please give me your mail Id.

[@StevieC82]

@@TEKNISHA great thanks. [e-mail]

[@ryanbailey273]

This tutorial is good for auto sorting with multiple columns: kzbin.info/www/bejne/e2HYoJ-mat2kfqM You have to pass an array of columns and an array of directions to the function.

[@JAMILAKKAS]

Is it possible sort data to another workbook

[@TEKNISHA]

Yes it is possible, but both workbooks need to be open (source and destination)

[@roie4500]

If they are equal?

[@TEKNISHA]

Both will have the same rank

[@rongenexplorer9514]

I tried it on my assignment in making payroll but it didn't work and came out with error.

[@buckyafitch]

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]

Please see the video. I have shown a workaround in it.

[@OOAK-Ireland]

what if you have equal numbers. then it won't work..

[@ankitrai9031]

How to sort if there are 3 sales column against same customer?

[@mohansinghnegi8360]

Thanks to you.

[@TEKNISHA]

Cheers!!

[@SMEEST55]

Use a separate disconnected pc, then using macro's is no issue. Formulae are slow. License fees may be a problem.

[@cheryltowns9297]

How to sort the Fx column?

[@longterminvestor9764]

SIR I WRITE 2020 IN EXCEL THEN IT CHANGE 20.20 AUTOMATICALLY THEN WHAT SHOULD I DO NARENDRA SINGH

[@behrangagin2918]

What if we want to add new names and records?

[@TEKNISHA]

Hello Behrang, You will have to update the reference in the formula

[@nazarkamal8831]

Different very new to me

[@aditicreationangel846]

Plse explain the same thing in form of row .You have taken example by taking row.So plse...

[@blavena]

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

[@rasikagunarathna846]

❤❤❤❤

[@TEKNISHA]

Glad that you liked it

[@ishutiwari5567]

this is very complicated 🤯

[@TEKNISHA]

Hello Ishu. This method may take a little time to setup, but gives you great results....

[@raguljayakumar1177]

Hello boss..we need a video which require sorting based on another data.. This tutorial is not useful..

[@YouTubeshortsandreelsface]

Same value then

[@khumbozulu9432]

Creativity

[@TEKNISHA]

Thank you

[@asrafssb]

I Like it. Thanks. SUB****BED

[@TEKNISHA]

Glad that you liked it. Thank you for subscribing

[@tommyharris5817]

Too long winded, waste of time

[@innesconnor7775]

Very helpful, thanks

[@TEKNISHA]

You are very welcome

[@tranduy8553]

Thank you much

[@TEKNISHA]

Glad that this helped

[@SenthilKumar-zq3oe]

Thank you very much

[@ziadhasan9179]

Thanks a lot