Formula to sort in alphabetical order

KaiKai

Board Regular
Hi!

I have some data in A2:Axx, it could be like this:
fgh;;;xyz;;stu;mno;;;;;;;;;xyz;;;;abc;abc;;def;;;;;;abc
(semicolons represent blanks)

Now I need a formula in B2 that sorts my data in alphabetical order, disregarding the blanks, so that B2:Byy is like

abc;abc;abc;def;fgh;mno;stu;xyz;xyz;;;;;;...

Is there a way?
I mustn't use VBA, since the file is to be sent to very weird people with strange options to activate any VBA...

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

lenze

Legend
Can't you just select your range and hit the Sort button on the standard Tool Bar?

lenze

KaiKai

Board Regular
Hi lenze!

That'd be toooooo easy!
The "new" data in B2:Byy is used in some other sheets for several calculations.
I'm quite aware of Excel's power (but not everything, as you see), but my "opponent" is not, that's why!
It needs to change dynamicly.

Do you have an idea for that, too?

Thanks a lot!

lenze

Legend
So, record a macro copying column "A" to Column "B" and then sorting Column "B". Assign it to a button or Shortcut Key.

lenze

KaiKai

Board Regular
Hi lenze!

Sorry, but I mustn't use VBA!
That would be easy for you, for them and for me (of course).
The file still has to be dynamic, when I e-mailed it! Without VBA!
So the whole thing has to be done by formula(s).
I could have written a VBA-script within minutes, but that's, as I said, not possible!

So, I definitely need a solution based on a formula, please!

KaiKai

Board Regular
Hi again!

I have a formula to list my data without blanks:
{=IF(ROW(A1)>COUNTA(A:A),"",INDEX(A:A,SMALL(IF(A\$1:A\$1000<>"",ROW(\$1:\$1000)),ROW(A1))))}

But I still can't find a way to sort this in alphabetical order.
Maybe this formula helps to make it an "easier" problem.

Of course, I might use extra columns, since there are a lot available, but it should be solved with as few extra columns as possible, 'cause there are several columns that need to be sorted, each one for its own.

mikerickson

MrExcel MVP
I don't know if it counts a VBA, but there is a UDF in this thread:
that sorts. It will need to be tweeked to sort horizontaly, but if a UDF will work for you, let me know.

KaiKai

Board Regular
Hi Mike!

Absolutely NO VBA!
No UDF, no script, no macro, no whatever...
They don't accept anything that might course anything else...

I really need a solution that is only with a "normal" formula, please!
It has to be possible, please!!!

mikerickson

MrExcel MVP
Is there a maximum length to the strings?

KaiKai

Board Regular
No, not as far as I know.
But I think it should be 30 or less, probably even 20 or less per cell.

Replies
1
Views
191
Replies
9
Views
326
Replies
0
Views
940
Replies
10
Views
772
Replies
3
Views
234

1,191,134
Messages
5,984,872
Members
439,921
Latest member
Neocold

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back