Danny wants to enter a value and have all of the like values sort to the top of the list.One way to do this is to use the Filter in Excel 2010, but there are other ways to accomplish this task - and today, in Episode #1530, Bill shows us other methods...but maybe you have an awesome method to share!? To contact Bill with your method to accomplish this task you may email MrExcel [bill "at" mrexcel.com].
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1530 - “Sort by Substring.” Hey, welcome back to the MrExcel netcast.
I’m Bill Jelen.
I almost think this one should be a duel because I’m not really sure that I have the best way.
And if you know a better way, please shoot me a note, bill@mrexcel.com.
Danny sent this question and Danny wants to be able to enter a value.
For example, accounting in an input box and then have all of the accounting entries sort to the top of this list.
And I’m going to push back a little bit.
I’m going to say, “Why do you need to do that?
Is that you want to copy all the accounting entries, or you want to make some entry to the right of all the accounting entries?” Because if we’re just trying to do one of those two things, then, it would be easier especially if you have Excel 2010 to use the filter.
And in the filter, search for accounting.
So, there’s your text box and you get all the accounting entries now.
If we need it, for example, enter some value adjacent all those.
We could just do that.
That didn’t fill in the other items, or if you need to copy those records, we can just copy.
We don’t need to use ALT + ; to select the visible cells only.
And then CTRL + V to paste.
And we get all the accounting records.
Alright, so if you have to do one of those two things, then, I don’t think we need to sort.
Just the filter would work okay.
But, let’s say that you actually need to sort.
Then, I think what we’re going to have to do is we’re now to come outside, out here where you enter your value.
So, this is accounting and we’re going to create a little column called there equals search for accounting.
I’ll press F4 within this adjacent cell, =SEARCH($D$1,A2).
And that’s either going to give us a number or a number or NA.
Number means that there-- it is found.
NA means-- or #VALUE means it’s not found.
So, once we have that in, we can now just sort all the accounting entries, show up to the top.
Of course, they are re-sequenced based on where accounting shows up and the list.
So, I’m not a big fan of either of these.
If someone has a better idea of how you can sort to get all the accounting records to the top.
And then later on, if we have to do, for example, marketing would change that value, resort, and get those to the top.
It’s still not a very cool way.
I even tried to think some VBA that could do this.
Relatively, though, for VBA to do that sort.
Alright.
So, hey I want to thank Danny for sending that question in.
I want to thank you for stopping by.
See you next time.
Learn Excel from MrExcel podcast episode 1530 - “Sort by Substring.” Hey, welcome back to the MrExcel netcast.
I’m Bill Jelen.
I almost think this one should be a duel because I’m not really sure that I have the best way.
And if you know a better way, please shoot me a note, bill@mrexcel.com.
Danny sent this question and Danny wants to be able to enter a value.
For example, accounting in an input box and then have all of the accounting entries sort to the top of this list.
And I’m going to push back a little bit.
I’m going to say, “Why do you need to do that?
Is that you want to copy all the accounting entries, or you want to make some entry to the right of all the accounting entries?” Because if we’re just trying to do one of those two things, then, it would be easier especially if you have Excel 2010 to use the filter.
And in the filter, search for accounting.
So, there’s your text box and you get all the accounting entries now.
If we need it, for example, enter some value adjacent all those.
We could just do that.
That didn’t fill in the other items, or if you need to copy those records, we can just copy.
We don’t need to use ALT + ; to select the visible cells only.
And then CTRL + V to paste.
And we get all the accounting records.
Alright, so if you have to do one of those two things, then, I don’t think we need to sort.
Just the filter would work okay.
But, let’s say that you actually need to sort.
Then, I think what we’re going to have to do is we’re now to come outside, out here where you enter your value.
So, this is accounting and we’re going to create a little column called there equals search for accounting.
I’ll press F4 within this adjacent cell, =SEARCH($D$1,A2).
And that’s either going to give us a number or a number or NA.
Number means that there-- it is found.
NA means-- or #VALUE means it’s not found.
So, once we have that in, we can now just sort all the accounting entries, show up to the top.
Of course, they are re-sequenced based on where accounting shows up and the list.
So, I’m not a big fan of either of these.
If someone has a better idea of how you can sort to get all the accounting records to the top.
And then later on, if we have to do, for example, marketing would change that value, resort, and get those to the top.
It’s still not a very cool way.
I even tried to think some VBA that could do this.
Relatively, though, for VBA to do that sort.
Alright.
So, hey I want to thank Danny for sending that question in.
I want to thank you for stopping by.
See you next time.
