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
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.
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.
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.
I found this to be very helpful. However, I tried to work the unsorted data on one tab, and the sorted data on the next tab. I linked them very well upto the last point when empty spaces replace some names of values. I am stuck on this.
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
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 😂😂😂😂
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
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
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
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.
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 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.
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.
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
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?
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. :-)
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.
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
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?
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
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.
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
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.
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'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?
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
"=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!!
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-)
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 ???
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.
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
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
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!
God Bless you, i was looking for a formula since 2 months and i found it in this video. thank you
Best rank, repeats and index/match explained! Ever!
This video was GREAT in helping me create a spreadsheet that automatically sorts lap times for our upcoming auto racing simulator event! Thanks!!
This video deserves a very positive comment. Thanks so much for posting it!
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
Thank You Very Much.. You are doing great Job.
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.
Hello. Really good. Thanks a lot.
Would like to know about dymanic chart range for multiple columns values
Have never come across a tutor like you. long live :)
Thanks for the kind word Munawar. Stay tuned for upcoming videos.
Life saver, Thanks mate
Thanks for the video...😊
Next time pls share the practice file also😉🤗
Amazing!!! It helps me a lot. Thank you ^^
Good......👍
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.
Let me check it out. I’ve never heard of that error.
Thanks for watching.
T-E-CH
I want to ask if is possible to sort "Name and Surname" in the same time, if are in different columns. Thank you
I found this to be very helpful. However, I tried to work the unsorted data on one tab, and the sorted data on the next tab. I linked them very well upto the last point when empty spaces replace some names of values. I am stuck on this.
Like your tutorials, it is very captivating and short and sweet, hope you can make more videos!
a very impressive tutorial! many thanks for the demonstration...
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
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 ?
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 😂😂😂😂
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
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
Me too Tomás!
expert of excel, thankyou for sharing
It is my pleasure Yopi Yulian. I hope you found my video helpful.
T-E-CH
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
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
Dude... You are awesome.
Thanks dude. Glad you liked it.
I want to sort a particular column based on the rank of another column. I thank you in advance.
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.
Brilliant tips! Thank you!
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, "
Thank you so much.Excellent.
Thanks a lot
Thank you very much this is what i wanted
Hello Moses. Thanks for your comment. It is very rewarding to hear my video helped you.
Thanks for watching
T-E-CH
Excellent!
Hello Jorge. Good to hear you liked the video. Please watch our other videos. Thank you.
T-E-CH
masha ALLAH
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.
Excellent
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.
Exellent!
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
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?
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
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. :-)
How can I automatically sort data if in a column the data has empty rows in between?
Thanks sir
I need help with automatic sorting
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.
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
can you remove the duplicated word like Germany in your SORT DATA?
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?
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
@@donwilcox7919 I will look into it.. Thank you so much.. This is awesome..
sir....tutor is very nice and helpfull.... but didnt understand logic of formula returning ans...pls help
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.
The Excel Challenge video name is automatically sort data alphabetical....
Sir.... formulas that you given...1 st count if....
And thank you so much for replying...
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.
It's my pleasure Alpesh. I'm trying to respond to all comments...
could you help me know ..about how to auto sort dates in entered in excel table. please do it as soon as possible. thanks.
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
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.
Dear I want to pick automaticallay other values in ranked data of the row.
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
information is good
Thank you Dhananjay.
T-E-CH
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?
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
Can you please help look up tied or duplicates values by Rank
France 80
US 60
Uk 80
China 66
Spain 60
👍👍👍👍👍
And now you can just use =SORT (well.... if you're part of the 'Insider' program)
can you expalin the formula used for "Rank"?
Yes I too want to know
"=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!!
Hello Benjamin. Too funny to hear "you struck gold".
I'm glad this video was helpful.
Thanks for watching.
T-E-CH
Without excel sheet?
How can you do this, and get it to ignore "Blank" cells??
You can use an IF(cell 0 Condition. 0 means “does not equal 0.
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...??
Send me your file with notes to theexcelchallenge@gmail.com and I’ll see what you mean.
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-)
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
???
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.
But sometime rank not comes proper
please check the video quality is not good. blurred view.
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
this is 720p, how is it bad quality
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