Sort One Column Challenge In Excel - 2413

Can you sort one column in an Excel table using the Filters and not sort the other columns? This would be easy if it was a range. But it you press Ctrl+T, I am not sure of a good way.
So - the weekend challenge: Does anyone have a clever way of solving this?

Transcript of the video:

Alright, here's a weekend challenge.
Can we can sort one column in a table using a filter without sorting the other columns?
Welcome back to the MrExcel netcast.
I'm Bill Jelen. You know, one of the things I've been doing for eight years or so is trying to answer questions, Excel questions on Quora, the website Quora.
I've done it enough that they send me probably two or three emails a day and always take a look at the questions and see if I happen to have an easy answer.
Maybe I could put into a video on YouTube or something like that.
And yesterday question came across my desk that was so bizarre.
"Can you sort a single column in an Excel table using a filter without sorting all the other columns in the table"?
And what I have found on Quora is the first thing you have to do is resist the urge to ask "why would you want to do this?" Or "this seems like such a horribly bad idea".
And then, second you have to try and figure out what they mean.
I haven't posted any questions on Quora, but I'm pretty sure that is limited to like just a sentence or two.
Because you never get anyone who actually explains more about what they're trying to do.
And this question is super interesting.
Its Can you sort just a single column.
In an Excel table.
Using a filter.
That's super interesting.
Why do we have to use the filter?
Without sorting all the other columns in the table?
And as I try to parse that apart, the capitalization of the word table is really important to me.
I mean it's not that they typed in all lower case.
They did capitalize 2 words here and so that lower case table makes me think that maybe it's just a range and not a capital T table.
Which is what I use when I press control T.
So let's take a look at the easy one, first Let's say we want to sort by cherry and not everything else.
And I understand this is a horrible idea.
It's just a challenge.
Can we do something crazy?
And so what I'm going to do is I am going to select just the Cherry column first and then do Data, Filter.
Which is interesting that it applies….
Isn't this weird to see a filtered data set with just that one column filtered?
And then I open this drop down and say, sort largest to smallest or smallest to largest.
Now this is such a horribly bad idea that Excel is going to nag us and say, you know, hey, it doesn't seem like you really want to do this.
Can we expand the selection?
And you have to say no.
I have a specific reason why I want to do this.
You know, maybe the specific reason is they want to randomly assign tasks.
Maybe they have some names here or something like that.
I would do that with index and RANDBETWEEN, but that's not the question.
Can you sort the single column in an Excel table using a filter without sorting all the other columns in the table?
And we sort and bam it works beautifully.
But the nagging issue here: When they said table, did they mean that they've press Control T table?
And when it is a Control+T table, I can't find any way to do this.
I even tried to trick Excel by selecting something that starts outside of the table and ends outside of the table.
So coming up here and selected this whole thing.
But that is just, you know, Excel is not going to be tricked.
This is all grayed out.
There's no way to do this.
I hate that sentence.
As soon as I write or say "there is no way to do this".
five people will pop out of the woodwork with a way to do this.
So, here's the question - a weekend challenge for you.
Can you find some way to sort a single column in an Excel Table using a filter without sorting all the other columns in the table?
And I think I'm even willing to take out the "using a filter".
Is there some way that we can just reassign those numbers other than coming out here, Control C. Control V to paste.
Then sort.
And then control C and control V.
You know, I even thought of maybe some clever formulas, but we don't have the things that Charles Williams has created.
The HSTACK or VSTACK to be able to get columns A&B in a formula, Column C sorted using the SORT function.
And then D&E and putting all those back together.
Yeah, I don't know so it's just one of those good weekend challenges.
Hey check out my new book MrExcel 2021, Unmasking Excel.
If you like these videos please, down below, Like, Subscribe, and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Thanks for stopping by.
I'll see you next time for another netcast from MrExcel.

 

[vcoolio]

Good day Bill,

With the challenge, which I've accidently come across, I suppose one could cheat a little using VBA:-

Sub TestSort()

        Dim lr As Long
        lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

'Assume the Table is in Sheet1(sheet code), from A1:E

        Sheet1.ListObjects("Table1").Unlist   '---->A bit naughty, but unlist the table."
        Sheet1.Range("C2", Sheet1.Range("C" & Sheet1.Rows.Count).End(xlUp)).Sort [C2], 1 '---->Sort Ascending.
        
        Sheet1.ListObjects.Add(xlSrcRange, Range("A1:E" & lr), , xlYes).Name = "Table1"  '---->Re-name the table.

Application.ScreenUpdating = True

End Sub

It's not really that different from you extracting the column values, sorting them and then placing them back into the Table but it all happens very quickly so it wouldn't be that obvious to the naked eye!

Cheerio,
vcoolio.

 

[MrExcel]

That is super amazing. I've never used Unlist before. Thanks!

 

[vcoolio]

You're welcome Bill and thanks for the acknowledgement.

Cheerio,
vcoolio.