sort 12 strings into alphabetical order

[merlin777]

I have 12 cells, e.g. A1 to A12, each with a text string (which come from elsewhere in the workbook).

I want a formula to make the 12 strings appear sorted into alphabetical order in a13 to a24.

I've posted the request before including blank cell removal but in spite of some great contributions we never arrived at a solution so I've worked on simplifying the requirement by removing the blanks another way.

All help greatly appreciated!

 

[merlin777]

I copied your code and changed the 549 to 557. The range changed to 'noblankrangemon' as before. I'm getting the same result as before.

There isn't any reason why it would work with a formula in the cell rather than directly typed text is there?

 

[merlin777]

I've just tried deleting and re-entering it. Oddly, excel has stopped changing the array to the array name.

 

[merlin777]

I tried changing my original data set to some text I entered manually to see what would happen. My single line of text in the results range has moved up one but here's the bizarre thing - it's not showing a string from my data set. The only place this appears in this column L is row 55.

Excel Workbook
	L
546	mon
547	tue
548	wed
549	thu
550	fri
551	sat
552	jan
553	feb
554	mar
555	apr
556	may
557	jun
558	11:30physiotherapy/Nursey NurseCHANGE DRESSINGS
559
560
561
562
563
564
565
566
567
568
569
Sheet1

 

[merlin777]

the 0 at the end of the last 12 formulae is actually a 1 as per your formula (I mixed up my pastings) but the result was the same with a 1 or a 0.

 

[mikerickson]

should that be $L$546:$L$$557 rather than 549?

The range should be the size of the cells being sorted.

 

[vlopez]

Agree with mikerickson

 

[merlin777]

do you think that these cells holdiing a formula that returns text rather that text itself should make a difference?

 

[merlin777]

sorted!

i needed to add a '+500' here:

=INDEX($L:$L,500+ 100*MOD

After some investigating I found the formula was sorting L46:L57 rather than L546:L547. I don't really understand the formula so I'm not sure why but this works...