Excel Automatically Sort When Data Changes or Added | Auto Sort Excel Formula | Auto Sort Macro
Вставка
- Опубліковано 26 сер 2024
- Download the featured file here www.bluepecant...
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.simpleshee...
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.
------------------------
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.
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!
Hey! This is great!
Is there anyway you could show how to auto sort a table of data based on a date?
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.
what if i wanna give a custom order, my own list according to which it ill get shorted
This is great! Tried this and it worked on my file. Thanks a lot.
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.
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
Excellent tutorial. I think than in method 2 it is better to use the "
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?
Love the webpage. Might be good to add an example of a two-column sort, which I needed.
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.
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.
I know nothing about Excel and this just blew my mind...
Awesome tutorial, when I get a tie I seem to loose a name in sales person column?
Perfect tutorial, thank you so much
Thank you, there are more then 2 in Rank, what should we do in TIE, =IF(COUNTIF)>1
Use this formula instead to account for more than one duplicate value in the "RANK" column:
=[@RANK] + (COUNTIF($C$2:[@RANK],[@RANK]) -1) *1
What formula would you use if you had more than 1 record that was a tie when you did the countif?
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?
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
@@joshalbert8510 good tip!
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?
What happens if I want to use this code for multiple tables in one single worksheet? How can I go about this?
Thank you very useful
Has the VBA code been removed? I'm not seeing it.
So helpful!
Thank you for the information. Worked very well for my query.
This is like like Flying to Italy to buy an Olive oil than simply walking to your nearest Local market and purchasing one.
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?
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?
This is incredible! Thank you so much. I can't tell you how big a help this is :)
You da man! 🎉
nice. please post video about sumproduct multi criteria, date range.
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?
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.
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?
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.
Great tutorial. How can to ascending order?
Thank you Sir, its really helpful for my analysis... :)
How do you solve ties if three or more people have the same score/salary?
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?
Good work
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
Excellent!
If i don't have 364
And am null in vba
Is there another solution
Please reply
Thnx
What happens when there is more then 1 tie.
Use this formula in the "Tie? column instead (adjust as necessary)
=[@RANK] + (COUNTIF($C$2:[@RANK],[@RANK]) -1) *1
@@RandyNolson Genius
just comes up with #Value when i try and make that second table to sort..
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!
Run-time error '1004': Method 'Range' of object '_Worksheet' failed
nice
pls i need
help
What if there are three ties or more?
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.
@@will07fca30 Same problem here - pulling my hair out