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

Hi,

How can I copy values from one column, they are spread down the column, with a lot of empty cells in between, but all cells contain a formula which needs to remain in the cell where the data is copied from. All values should then be copied to the next column, but be compiled and sorted alphabetically and without any empty celles in between. This operation should take place automatically...!

Best regards

Jesper


<<Original title: Copy values from one column to next, but compile and remove empty cells.
I changed it to the more descriptive title shown above.
byundt--Excel TA Page Editor>>
jemagnussenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bruintjeCommented:
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
byundtMechanical EngineerCommented:
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
byundtMechanical EngineerCommented:
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
 Acronis Global Cyber Summit 2019 in Miami

The Acronis Global Cyber Summit 2019 will be held at the Fontainebleau Miami Beach Resort on October 13–16, 2019, and it promises to be the must-attend event for IT infrastructure managers, CIOs, service providers, value-added resellers, ISVs, and developers.

jemagnussenAuthor Commented:
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)...
jemagnussenAuthor Commented:
Further I should mention that she has a formula in the cells in the column with the original data.

Regards

Jesper
byundtMechanical EngineerCommented:
Jesper and Vibeke,
If the cells contain numbers, then the SMALL function is all you really need:
=SMALL(NumberList,1)         returns the smallest number in named range NumberList
=SMALL(NumberList,2)         returns the second smallest number        
=SMALL(NumberList,3)         returns the third smallest number

To get the series 1, 2, 3 ..., you can use ROW()-mmmm, where mmmm is one less than the row number of the first cell with the formula.
=IF(COUNT(NumberList)<ROW()-mmmm,"",SMALL(NumberList,ROW()-mmmm))
This formula returns an empty string when the list is exhausted, otherwise it returns the numbers contained in NumberList in ascending order

Sample workbook showing all the formulas in action: http://home.mchsi.com/~byundt/AlphabeticalOrderFormulaQ21731705.xls

Brad

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jemagnussenAuthor Commented:
Thank you Brad,

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

Have a nice weekend.


Jesper

byundtMechanical EngineerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Software

From novice to tech pro — start learning today.