How to sort text data in a row

[prettysmart]

E1S E1S H1 AAA1 ME21 SL19

E1S E1S H1 AAA1 SL19 ME21

AAA1F H1 AAH1 E1 SL19 ME21

H1 E1 ME21 AAA1 AAH1 SL19


Can you help me write a formula to sort these text data horizontally?


Thank you so much.

 

[prettysmart]

columnA columnB columnC columnD columnE columnF


row 2 E1S E1S H1 AAA1 ME21 SL19

row 3 E1S E1S H1 AAA1 SL19 ME21

row 4 AAA1F H1 AAH1 E1 SL19 ME21

row 5 H1 E1 ME21 AAA1 AAH1 SL19


Sorry for the last post. The table should look like this. I was able to use =small($A2:$f2,column($m2:m2)) to sort numbers, but small does not sort text. Need help.


Can anyone here show me how to use formulas to sort these data horizonatally in each row?


Thanks.

 

[kensbro]

Hi-In the "Sort" dialogue box there is an "Options" button. It allows you to sort horizontally. Hope this helps.

 

[prettysmart]

Thanks for your quick reply. But I have more than 2000 row to sort. It will take me a lot of time to do each one one at a time. Please show me a formula.


Thanks.

 

[jeremymjp]

Have you recorded a macro? If you've never tried, this may be a good instance to test it out. It's not as hard as it seems.


Begin with the cursor in the first cell of the first row you want to sort. Then, click "record macro" on the developer tab. [If you don't have the developer tab, go to Excel options, then General tab, then check the box to enable.]


Before you do anything, click the box that says "Relative Reference". Then, select your first row, perform the sort as kensbro described above, then click the very next row's FIRST cell. Press stop.


When you're setting up the record, give it an easy hotkey so that you can just hold down the set of hotkeys and it will scroll down your 2000 rows pretty quickly.


Even better, if you'd like to try to write a for loop in VBA, you wouldn't have to hold down your macro's hotkeys.


Branch out!

 

[prettysmart]

thank you so much. It works great. But may I ask if you also show me a formula so that I don't have to press the ctl + key shortcut in case I have thousands of records?

 

[Luke M]

By putting in the loop, you only need to run the macro once. You should be able to use this macro as is.

[pre]

Sub SortHorizontal()
Dim xRow As Integer
xRow = Range("A65536").End(xlUp).Row

Application.ScreenUpdating = False
'Loop through each row to sort
For i = 2 To xRow
Cells(i, 1).EntireRow.Sort Key1:=Cells(i, 1), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
Next
Application.ScreenUpdating = True
End Sub

[/pre]

 

[jeremymjp]

I'll give it a try.


With your workbook open, press Alt+F11. This opens the VBA editor so that you can manually create and edit code.


In the top-lefthand corner, you'll see a small window in "tree view". At the bottom of those options will probably be the word "Module1". Click to expand it. Double-click the contents.


A text-ish looking file will open to the right. You'll see the macro you recorded. It will probably be called "Macro1".


Above the line that reads "Sub Macro1()", type this and press enter (don't include the asterisks):

***

Sub RepeatMacro1()

***

You entered the head of the code, it will automatically give it a closing. Between those two points, paste this:

***

Dim Counter as Integer

Dim NumTimes as Integer


NumTimes = 1000 '''Or however many lines you want to perform this action


For Counter = 1 to NumTimes

Run ("Macro1)

Next Counter

***


I think that works...


Then close the editor and run the RptMacro1 macro. If you can't find it, open the "Macros" box from the developer tab, select it, then press run.


Hope it does...

 

[prettysmart]

By putting in the loop, you only need to run the macro once. You should be able to use this macro as is.


Sub SortHorizontal()

Dim xRow As Integer

xRow = Range("A65536").End(xlUp).Row


Application.ScreenUpdating = False

'Loop through each row to sort

For i = 2 To xRow

Cells(i, 1).EntireRow.Sort Key1:=Cells(i, 1), Order1:=xlAscending, Header:=xlNo, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _

DataOption1:=xlSortNormal

Next

Application.ScreenUpdating = True

End Sub


The above macro works really great. My question now is I don't want to sort the entire column. I just just to sort each row from column F to column R, and keep the first few columns intact because they contain names and some particulars that need no change.

Please help.

 

[SirJB7]

Hi, prettysmart!

Try to replace "Cells(i, 1).EntireRow" to "Range(Cells(i, X), Cells(i, Y))", where X and Y are the numbers of the first and the last columns to be sorted. Please note that maybe you'll have to adjust your sort criteria: "Key1:=Cells(i, 1)".

Regards!

 

[prettysmart]

It works great. thank you so much for your help.