Sorting a list/values that are spread out over multiple columns

I am trying to alphabetically sort a list that spans multiple columns in Excel and while it seems like an easy task, I have not found a way to do it. For argument's sake, let's say there are 9 cells/values in the list and that it begins in A1 and ends in C3. I can sort any row or column, but I'd like to sort the entire range so that the lowest value is in A1 and the highest value is in C3. Multicolumn lists can be sorted with relative ease in Word, I expect because the list is interpreted as a table. Perhaps the solution lies therein?

Thank you!

Hi
A forum guru might find a more elegant solution, but this macro seems to work.

Please Login or Register  to view this content.

Hi Alan, attached are what the data looks like before and what I'd like it to look like afterwards.

Ah
my previous macro assumed numbers not strings
try this one:

Please Login or Register  to view this content.

Thanks FlameRetired. OK, I see how it works using the formula. Your way is very specific and does exactly what I showed in the before and after spreadsheet. If I want to adapt it, I just change the values of the starting cell in the formula where I want the sorted table to begin and the paste the formula in the new sorted table cells. The macro that NickyC sent had the added advantage of being a little more generic/versatile but for very specific examples, yours works perfectly. Thank you for the original and improved formulas, I really appreciate your help!!

Hi
apologies for not getting back to you sooner - try this

Please Login or Register  to view this content.

Here is a derivative of FlameRetired's post #6 formula that is a little shorter and simpler and - as far as I can see - still works correctly. As with the formula in post #11 this formula will automatically adapt to the source starting anywhere in the sheet. There is also no need to enter it as an array formula.

Another consideration in putting this to practical use would be how the formula would have to change if the number of rows and/or columns in the source data were to change. Obviously all $A$1:$C$3 references must be changed as necessary. In addition the 3 in "*3" (both instances) must be changed to the number of rows in the source data. The number of elements in the {1;1;1} vertical array constant must be changed to the number of columns in the source data and the number of elements in the {1,1,1} horizontal array constant must be changed to the number of rows in the source data. Similar considerations would apply to the other formulas noted above.

Finally note that I think there is a limitation that all of these formulas break if there is a cell in the source data that is numeric rather than alpha.

Formula:

Please Login or Register  to view this content.