Automatically sort text in one column into alphabetical order in another column

Hello jemagnussen,

you could try something like
--------------

Sub Test()
Dim sh As Worksheet
Dim wbk As Workbook
Dim rng As Range

  Set wbk = ThisWorkbook
  Set sh = wbk.ActiveSheet
 
  ' get the last used cell in the column A
  sh.Range("A65000").Select
  Selection.End(xlUp).Select
 
  ' set range object to part to copy
  Set rng = sh.Range(Selection, sh.Cells(1, 1))
  rng.Copy
 
  ' paste in the column beside only values no formulas
  rng.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
  Application.CutCopyMode = False
   
  ' sort descending
  rng.Offset(0, 1).Sort Key1:=rng.Offset(0, 1).Cells(1, 1), Order1:=xlDescending
 
  ' cleanup
  Set rng = Nothing
  Set sh = Nothing
  Set wbk = Nothing
 
End Sub

--------------

hope this helps a bit
bruintje

Hi Jesper,
Here is an array formula that will produce your list, returning empty strings ="" once all your test data has been exhausted. The tricky part was getting the formula to sort the data in alphabetical order.
=IF(COUNTA(A$1:A$5)-COUNTBLANK(A$1:A$5)<ROW(),"",INDEX(A$1:A$5,MIN(IF((ROW()+COUNTBLANK(A$1:A$5))=COUNTIF(A$1:A$5,"<="&A$1:A$5),ROW(A$1:A$5),""))))
The reference to row 5 is to the end of your data.

My sample data was in cells A1:A5, and the formula was put in cells C1 and copied down. Formula performance depends heavily on location of the formula and your data in your worksheet--it will surely need to be tweaked if you have it anywhere else. Here is a sample workbook showing the formula in action: http://home.mchsi.com/~byundt/AlphabeticalOrderFormulaQ21731705.xls

Brad

Jesper,
Here is a revised array formula that allows your data to start in any row and your alphabetical summary likewise to start in any row. It also allows the formula range to extend beyond your data, so you can add more in the future and have it automatically alphabetized. With the new formula, you may drag both your data and the formulas anywhere you wish, and the formulas will automatically adjust to the new location. Sample workbook showing it in action: http://home.mchsi.com/~byundt/AlphabeticalOrderFormulaQ21731705.xls

=IF(COUNTIF(List,"?*")<ROW()-mmm,"",INDEX(List,MIN(IF((ROW()-mmm+COUNTA(List)-COUNTIF(List,"?*"))=COUNTIF(List,"<="&List),ROW(List)-nnn,""))))
To create an array formula, hold the Control and Shift keys down, then press Enter. Excel should respond by adding curly braces { } surrounding the formula. If it doesn't, then select the cell, click in the formula bar and CTRL + Shift + Enter.

In this formula, there are three named ranges:
List          Source data to be alphabetized. May include blank cells and empty strings ="". May extend beyond your actual data.
mmm      Number of row before your formula starts.I populate this cell with the formula     =ROW(G15)-1        (G15 being the first cell containing the formula)
nnn          Number of row before List starts. I populated this cell with the formula     =ROW(List)-1

Brad

Hi both of you,

Thank's for the quick respons. I must admit that I like Brad's approach better, since its my wife that needs this, and she is not a "VB Guy"... :-)

She finds that your first suggestion works for her, its fairly simple and she can define excatly how many rows to include and the rows i question.

Only problem is that it needs to work with figures. And I must admit that I'm to blame for the mistake, writing that it need to be sortet alfabeticaly...! Just sorted.

Please show us how you do this for numbers only.

Best regards

Jesper (and Vibeke)...

Further I should mention that she has a formula in the cells in the column with the original data.

Regards

Jesper

Thank you Brad,

As always - a good solution. Just what we needed...

Have a nice weekend.


Jesper

Jesper,
I'm actually glad that the real problem was misunderstood. Developing a formula to sort text into alphabetical order was a very interesting challenge--and I've bookmarked the question for future reference.

Thanks for the grade!
Brad