Excel Automatically Sort When Data Changes or Added | Auto Sort Excel Formula | Auto Sort Macro
Тәжірибелік нұсқаулар және стиль
Download the featured file here www.bluepecantraining.com/exc...
Need pre-built Excel spreadsheet solutions? Try Simple Sheets - over 100 templates covering your personal and work-based needs including Gantt charts, personal budgets, employee scheduling, timesheets and more… www.simplesheets.co/a/45451/v...
By clicking on this link you are helping to support this blog - thanks!
In this video I demonstrate how to automatically sort data when it is changed or when new data is added. The video covers the following methods:
1) Automatically sort numeric data using the RANK, INDEX and MATCH. Deal with ties using IF and COUNTIF.
2) Automatically sort text data using COUNTIF, INDEX and MATCH. Deal with ties using IF and COUNTIF.
3) Automatically sort any data in Excel 365 using the SORT function.
4) Automatically sort any data in situ using a VBA macro.
------------------------
Пікірлер: 57
Looked really hard for something similar to auto sort for one of my projects and at last, i stumbled upon your video. Really great tutorial, my query is solved now!
Excellent tutorial. I think than in method 2 it is better to use the "
What do you do when you have more than 2 with the same criteria? This method appears to only work for a max of 2 at a time. For instance I want to sort by Date and I have >2 with the same date.
This is incredible! Thank you so much. I can't tell you how big a help this is :)
This is great! Tried this and it worked on my file. Thanks a lot.
Hey! This is great! Is there anyway you could show how to auto sort a table of data based on a date?
Love the webpage. Might be good to add an example of a two-column sort, which I needed.
Thank you for the information. Worked very well for my query.
Thanks. Great solutions. One question though. Would it possible to have the vba for sorting based on two columns for example first column B and then column a. Thanks.
Thank you for this video. One issue I couldn't have solved it when the table is provided with a function that generates on it constantly an update. The table doesn't sort automatically in that case.
Really handy video, thanks. Quick tip using the final VBA method: add another variable, _DataCol_ Like this: _Set DataCol = Range("MySheet[ColumnX]")_ _Set SortCol = Range("MySheet[ColumnY]")_ Then change _If Not Intersect(Target, SortCol) Is Nothing_ *to* _If Not Intersect(Target,_ *DatatCol*_) Is Nothing_ This allows you to sort by Column Y as data is entered into Column X Why? It allowed me to sort a sheet by date of data entry for certain fields. Just thought this might be useful for others.
@roronoazoro8343
Жыл бұрын
what if i wanna give a custom order, my own list according to which it ill get shorted
Great video, is this method still possible if, using your example, the sales were calculated by an =average formula using data from previous columns within the same row?
Perfect tutorial, thank you so much
nice. please post video about sumproduct multi criteria, date range.
Thank you Sir, its really helpful for my analysis... :)
my table contains hyperlinks referenced to other cells. What i type in those cells is what is displayed in the table as a friendly names of the hyperlinks, however the code doesn't detect indirect change i suppose because it doesn't sort it unless I interact with it directly.
So helpful!
Thank you very useful
Awesome tutorial, when I get a tie I seem to loose a name in sales person column?
THis is brilliant thankyou. But when i am ranking by date i have multiple dates which are the same can i assign a unique rank number as i have multiple repeated.
How to sort rows of different colors in excel? Suppose I want to keep red rows at top, yellow rows at middle and white at last? Everytime I change the color of row, I have to manually sort it. Is there any way to sort it automatically? Thankyou
Is there a way to do this when its sorting more information? I have a list of projects where the due dates change as do the priority (High, Medium, Low). Is there a way to sort it first by Priority then by Due Date?
I have a table with 29 rows. Two of those rows contain formulas in the sort column. When I change a cell external to the table, the formulas recalculate, however, the table doesn't auto sort It doesn't seem to recognise a calculated cell in the table sort column changed. If I change a different cell in that column (a non calculated cell) the sort happens. Is there a way for the code to recognize that a calculated cell changed?
This is like like Flying to Italy to buy an Olive oil than simply walking to your nearest Local market and purchasing one.
Excellent!
What formula would you use if you had more than 1 record that was a tie when you did the countif?
Hi Chester, would the first solution work if the data in the table is live data and it changes in real time? Would the sorting still be in real time in the second table?
Great tutorial. How can to ascending order?
Thank you, there are more then 2 in Rank, what should we do in TIE, =IF(COUNTIF)>1
@RandyNolson
2 жыл бұрын
Use this formula instead to account for more than one duplicate value in the "RANK" column: =[@RANK] + (COUNTIF($C$2:[@RANK],[@RANK]) -1) *1
You da man! 🎉
I know nothing about Excel and this just blew my mind...
Has the VBA code been removed? I'm not seeing it.
how do you write the VBA to allow inserting a new row?
How does the code change when I want to use this for multiple tables within the same sheet?
Thank you so much for the information. My excel sheets are working perfectly! The only issue is when I have more than 2 with the same criteria, then the 3rd one would be blank. Do you have any solution for it?
@joshalbert8510
Жыл бұрын
Randy Nolson Randy Nolson 6 months ago Use this formula instead to account for more than one duplicate value in the "RANK" column: =[@RANK] + (COUNTIF($C$2:[@RANK],[@RANK]) -1) *1
@pichouille29
Жыл бұрын
@@joshalbert8510 good tip!
Good work
How do you solve ties if three or more people have the same score/salary?
How can I sort by priority of three columns inline using VBA? Say I have days, hours, minutes, How do I auto sort by days, then hours, then minutes?
having 2 3rds and no 4th makes perfect sense, what doesnt make any sense is ranking differently the exact same value... anyways, great tutorial, very helpful
What about when you need to sort text data by list. Meaning I have Type A, Type B, and Type C (not alphabetical) and need it sorted in that order. How do I do that?
So when I use the Tie formula =IF(COUNTIF($C$2:[@Rank],[@Rank])>1,[@Rank]+1,[@Rank]), it does the correct action by adding 1. The problem is, now, the tied value adds +1, but now instead of having two 3's, I have two 4's.
What happens if I want to use this code for multiple tables in one single worksheet? How can I go about this?
If i don't have 364 And am null in vba Is there another solution Please reply Thnx
Can anyone help? I am trying to VBA method and keep getting this error message. Is it because the column I am trying to sort by contains dates rather than currency? If so is there a way to easily modify this code to make it work for dates? Thank you in advance!
@hollysmith2264
Жыл бұрын
Run-time error '1004': Method 'Range' of object '_Worksheet' failed
just comes up with #Value when i try and make that second table to sort..
nice pls i need help
What happens when there is more then 1 tie.
@RandyNolson
2 жыл бұрын
Use this formula in the "Tie? column instead (adjust as necessary) =[@RANK] + (COUNTIF($C$2:[@RANK],[@RANK]) -1) *1
@mikezhang8861
9 ай бұрын
@@RandyNolson Genius
What if there are three ties or more?
@will07fca30
Жыл бұрын
Jack, I have the same question. Great tutorial but I am using this for a quiz outcome and in some occasions I have 10 players with 5 of them having the same number of points. What then seems to happen is the resulting form starts missing names and results. No clue how to fix this.
@wayneredler2455
Жыл бұрын
@@will07fca30 Same problem here - pulling my hair out