TECH-006 - Create a list that automatically sorts data (alphabetically) as you enter data in Excel
YOU CAN DOWNLOAD ALL MY FILES FOR FREE AT www.theexcelchallenge.com
Learn how to create a spreadsheet that automatically sorts data (alphabetically) as you enter data in Excel
In this video you will use the COUNTIF formula, the MATCH formula, the INDEX formula, the IFERROR formula, as well as 'Less Than' conditions, etc.
I hope this tool is something you can use in your daily routine so you get more with less effort.
If you have a challenge with Excel that you need help with, whether it is at work, at home, or at school, please write it in the comments below. I try to resolve common Excel challenges from our viewers and subscribers.
Thank you for watching.
Check out some of our previous videos in the links below:
* TECH-005 - Create a quick and simple Time Line (Gantt Chart) in Excel
• TECH-005 - Create a qu...
* TECH-004 - Calculate total days between dates in Excel
• TECH-004 Calculate tot...
* TECH-003 - Display images in comments
• TECH-003 - Insert imag...
Пікірлер: 110
You saved my life bro! I have been struggling with editing my references for my dissertation, and now I have a programmed worksheet for my entire life use. Appreciate your effort
Whoa, that's my geek-fix for the day!! Excellent, so much learning in that little project!
Best rank, repeats and index/match explained! Ever!
Like your tutorials, it is very captivating and short and sweet, hope you can make more videos!
Brilliant tips! Thank you!
This video deserves a very positive comment. Thanks so much for posting it!
a very impressive tutorial! many thanks for the demonstration...
This video was GREAT in helping me create a spreadsheet that automatically sorts lap times for our upcoming auto racing simulator event! Thanks!!
God Bless you, i was looking for a formula since 2 months and i found it in this video. thank you
Amazing!!! It helps me a lot. Thank you ^^
Thank you so much.Excellent.
Life saver, Thanks mate
Thank You Very Much.. You are doing great Job.
Have never come across a tutor like you. long live :)
@TheExcelChallenge
7 жыл бұрын
Thanks for the kind word Munawar. Stay tuned for upcoming videos.
Hi, having a question, if I have a sheet with data/information, in which one of the column is filled with the date the row of information being filled. Now I want to perhaps on a seperate tab or sheet dispay the above mentioned data all in the order of dates being entered, could u do a video showing how to do that? thank you very much in advance
Exellent!
REALLY THE SUPERB ONE, THIS IS WHAT I WAS SEARCHING FOR. THANKS, A TON I want to sort data in the same way but with multiple columns. Please guide me on how to use it with expanding the selection for automatically sorting other columns like Phone Number, Email address, etc.
Thank you very much
Hello. Really good. Thanks a lot.
Thanks a lot
Would like to know about dymanic chart range for multiple columns values
I want to sort a list of names and rank them from 1 - 50 automatically everytime I enter data. Each name has a score attached to the name in the next cell over. How can I get to do this.
Excellent
expert of excel, thankyou for sharing
@TheExcelChallenge
5 жыл бұрын
It is my pleasure Yopi Yulian. I hope you found my video helpful. T-E-CH
Thanks sir
I want to ask if is possible to sort "Name and Surname" in the same time, if are in different columns. Thank you
Dude... You are awesome.
@TheExcelChallenge
5 жыл бұрын
Thanks dude. Glad you liked it.
Thank you very much this is what i wanted
@TheExcelChallenge
5 жыл бұрын
Hello Moses. Thanks for your comment. It is very rewarding to hear my video helped you. Thanks for watching T-E-CH
Good......👍
Excellent!
@TheExcelChallenge
5 жыл бұрын
Hello Jorge. Good to hear you liked the video. Please watch our other videos. Thank you. T-E-CH
Great presentation! How would I use the sort data column to rank instead of by Alpha by number of occurrence's larger to smaller. Also what if the data (name) was not just country but other columns like state and city. I want to find matches from 2 or 3 columns country and state or county , state and city, ranked by number of occurrence's. Thanks so much.
I am trying create a spread sheet for a random draw tournament with no repeat partners. I have used the mrand formula and it works but the problem I have is not all of the time do I have and equal number of players so excel doesn't know what to do with the odd person. In our tournament lingo the odd person would get a "Bye" in the program that person dissappears.
Thanks for a great video, but is there a way to sort a list automatic alphabetical without the other columns, I have a spreadsheet with names, address, and phone. I would like if I could enter a name, address and phone in the next cell and when I push enter it will be placed correct alphabetical into the list
Thanks for the video...😊 Next time pls share the practice file also😉🤗
THANK YOU SO MUCH!!!!! GOSH! I've been searching for a way to do this for SO LONG and never found one that worked! Wish you a good long life for this 😂😂😂😂
@TheExcelChallenge
6 жыл бұрын
LOL... I loved your comment Tomás. I wish you a good long life too and I thank you for watching. Check out my other videos. I hope they help you as well. T-E-CH
@TheExcelChallenge
6 жыл бұрын
LOL... I loved your comment Tomás. I wish you a good long life too and I thank you for watching. Check out my other videos. I hope they help you as well. T-E-CH
@JORGEAPMENEZES
6 жыл бұрын
Me too Tomás!
Hi . I would like to ask you, What if we insert another row and how to auto change the rank repeat and so on..... formula ?
How can I do this if I do not want it to be alphabetically but linked to the rank with 1 at the top and 15 at the bottom
how can u move the data from first sheet to second sheet. u storing data on same sheet different table, instead using table can u do the data that u enter on first sheet to be stored on second sheet. then the total value from second sheet to sort data on third sheet. any thing would be very halpful thanks for the video
hi, i have a point table for various team which is automatic update from a different excel, now i wanna sort point table by score and run rate
masha ALLAH
information is good
@TheExcelChallenge
6 жыл бұрын
Thank you Dhananjay. T-E-CH
If I want to do a stock taking of a business and have a list alphabetically how do I go about doing this
Hello Can you do the same using dates - sorting by date - as dates become available. Let's say we have a list of fixtures to be played during a football season. The fixtures are known but the team are not. As the dates become available, we want to be able to type the date in the correct cell and then for all lines with available dates to automatically re-sort themselves from the most recent to the oldest. Thanks.
How can I do this but use peoples names rather than countries? The names have to be first name then surname but sort automatically based on the surname?
I want to sort a particular column based on the rank of another column. I thank you in advance.
Thank you for sharing awesome tips... That was super cool... Suppose i have lists of invoice for different home appliances in one sheet aka my stock list, and in the next sheet i have my daily sales records. Can my stock list get updated when i entered a product on my sales record sheet? Example: in sheet one row 100th i have Oven XXXX stock Balance =10, in sheet two row 13th i have a sales entry for Oven XXXX, Can my stock balance in Sheet one get automatically updated?
@donwilcox7919
5 жыл бұрын
Donnomuch, There are several ways to do this. You can use index match, vlookup and even vba. However, if you have oven xxx on multiple lines, I would usa a sumif. Now, It's better if you convert the 2 lists in to tables. (Click anywhere in your list on sales tab and hit Ctrl + T.) This normalizes your list. =sumifs ([salescount], [salesitem], [@[stockitem]]) You can also use index match with arrays. Don
@Donnomuch
5 жыл бұрын
@@donwilcox7919 I will look into it.. Thank you so much.. This is awesome..
could you help me know ..about how to auto sort dates in entered in excel table. please do it as soon as possible. thanks.
@TheExcelChallenge
6 жыл бұрын
Hello Dhananjay. To auto sort dates, please use the exact same logic I used in the video but instead of entering text in Column B, simply enter dates. You may need to format the cells in columns B and H (right click on the cells and go to Format Cells... then select the Number tab... and select Date... finally, pick one of the options in the Type list. Trust this will help you. T-E-CH
How can I automatically sort data if in a column the data has empty rows in between?
can you remove the duplicated word like Germany in your SORT DATA?
Sir this formula is really helped us but only concern is Its not getting sort out when there is no proper Rank No. For example if there is no 3 no.(Rank) then the particular place shows ZERO.
@TheExcelChallenge
4 ай бұрын
Let me check it out. I’ve never heard of that error. Thanks for watching. T-E-CH
👍👍👍👍👍
Dear I want to pick automaticallay other values in ranked data of the row.
@TheExcelChallenge
6 жыл бұрын
Hello Muhammad. I would love to help you. Please elaborate on your question. Clearly describe the problem and what you want to achieve and it will be my pleasure to respond. Thanks for watching. T-E-CH
I need help with automatic sorting
There is a problem with repetitions. Say you have a list with 6 Ranks but the item of the 5th Rank has 2 Repetitions. So the 5th Rank + Repetitions will be 5+1=6 and 5+2=7. If the 6th rank has no repetition then its Rank + Repetition value will be 6+1=7. It means that there will be two different items with the same Rank + Repetition values (=7) thus one item will be lost when looking up the Rank+Repetition value of 7 with the Index-Match formula. I think that the solution could be to insert another Rank column where the values of the COUNTIF($B$2:$B$16, "
I'm trying to use this technique with a table. The Name sorts automatically but the phone number (in the cell beside it) does not...is there any way to make the phone number move with the name?
@TheExcelChallenge
6 жыл бұрын
Yes. Have a small database with names and phone numbers. Then do your dynamic table and lookup phone numbers from your database. It should be pretty easy. If you need more help, email me your file and I’ll return it with a solution. T-E-CH
It’d be nice if you could help me with this challenge 😁. I manage a property, and sometimes I have to post notices on doors and don’t have time to sort the units I need to go to by building. I have a table on excel that contains the building numbers on the top row and their respective units below them. How would I go about making a formula that automatically takes units I type in and sorts them into their respective buildings on a different sheet? This is because the reports I get from our real estate software does not organize by building and simply spits out a list of units based on people who owe money and that’s it /: In any case. Thanks for the video above I will tinker with this.
@TheExcelChallenge
5 жыл бұрын
Hello Kevin and thanks for watching. Yes, it’ll be my pleasure to help you. Send me a file I can look at to theexcelchallenge@gmail.com and we’ll exchange emails there. T-E-CH
How to get G column could you please reply
And now you can just use =SORT (well.... if you're part of the 'Insider' program)
an excel challenge: text database on one sheet consisting of a 2 lists (one german, one english). i used the formulas above to make the lists update automatically: perfect! however the lists contain many duplicates; the duplicates are neccessary on the fist sheet. the lists with the duplicates will be tansported to another sheet, where the text will be needed only once, which means that the duplicates must be removed. hopefully you have a suggestion for me. :-)
Great videos!! Can you just (in short) explain what is this "growing range" B:B? I was seeing this on some videos but somehow always I am without proper answer.
can you expalin the formula used for "Rank"?
@malavikareddykatta2411
3 жыл бұрын
Yes I too want to know
Can you please help look up tied or duplicates values by Rank France 80 US 60 Uk 80 China 66 Spain 60
"=sort" formula in google sheets lol ffs!!!, I am trying to make a foolproof sheet for someone who uses Exel, I think I have struck gold... thank you!!
@TheExcelChallenge
6 жыл бұрын
Hello Benjamin. Too funny to hear "you struck gold". I'm glad this video was helpful. Thanks for watching. T-E-CH
sir....tutor is very nice and helpfull.... but didnt understand logic of formula returning ans...pls help
@TheExcelChallenge
6 жыл бұрын
Hello Alpesh. For sure I can answer. What is your specific question? Give me a time mark in the video that I can refer to.
@alpeshdoshi7827
6 жыл бұрын
The Excel Challenge video name is automatically sort data alphabetical.... Sir.... formulas that you given...1 st count if....
@alpeshdoshi7827
6 жыл бұрын
And thank you so much for replying...
@TheExcelChallenge
6 жыл бұрын
That's right Alpesh. Once you've entered all the formulas, then everything is sorted automatically. You will no longer have to sort manually. The concept behind the formulas is very simple: (1) rank the values and (2) untie any repeats. Feel free to check the video once again and if you have a specific question on a formula, please send it. Give me a time mark in the video when you send your question. Thanks for watching and stay tuned for more videos.
@TheExcelChallenge
6 жыл бұрын
It's my pleasure Alpesh. I'm trying to respond to all comments...
Without excel sheet?
But sometime rank not comes proper
First Table with 4 column. (DATE,NAME, ITEM, PRICE) 06.06.20, Santa, Item1, 35 07.06.20, Sydney, Item2, 50 08.06.20, Santa, Item1, 70 08.06.20, Sydney, Item1, 35 Second Table with 3 Column. (NAME, ITEM, TOTAL PRICE) Santa, Item1, 105 (i.e 35+70) Sydney, Item1, 35 Sydney, Item2, 50 (105, 35 & 50 this are the result should come by formula) . I want to match first table NAME & ITEM with Second table and get the TOTAL PRICE result as shown above. . Please help me to make such table.
How can you do this, and get it to ignore "Blank" cells??
@TheExcelChallenge
6 жыл бұрын
You can use an IF(cell 0 Condition. 0 means “does not equal 0.
@newtronbomb5
6 жыл бұрын
Thanx B-) But then...when you do this...you lose your "0" ranked piece of data...and/or your "first/top ranked" piece of data...??
@TheExcelChallenge
6 жыл бұрын
Send me your file with notes to theexcelchallenge@gmail.com and I’ll see what you mean.
@newtronbomb5
6 жыл бұрын
Sorry, I was in a rush when I sent that followup question...I was eventually able to figure out a way to get around the issue. Thanks for your help!! B-)
@newtronbomb5
6 жыл бұрын
Actually, I have come up with a new challenge...without the need for writing a macro...how do you eliminate duplicate values in multiple columns?? So for example...how do you turn the following Tom Brad Jane Jane Jane Tom into: Tom Brad Jane ???
please check the video quality is not good. blurred view.
@TheExcelChallenge
6 жыл бұрын
Hello Dhananjay and thanks for your comment. I put a lot of effort in delivering a good quality video, and I appreciate feedback like this so I can improve, rather than just hitting the "dislike" button. If you wouldn't mind, please let me know what device you're using and if it shows blurry all throughout the video or just in some sections. I will look into this problem. Thanks again T-E-CH
@rtpntportugal
6 жыл бұрын
this is 720p, how is it bad quality
@TheExcelChallenge
6 жыл бұрын
I've been looking into this and it looks just fine on my different devices (tablet, smartphone, laptop, and smart TV). Not sure Dhananjay... maybe your internet connection?? T-E-CH