Formula to sort 6 row values into ascending order

D_Spark

Board Regular
Joined
Feb 4, 2007
Messages
232
I have a workbook with 120 rows

Each row has 6 columns, so 6 numeric values per row

I want to add a formula into cells (G1:L1) to sort the first row values into ascending order and then drag the formula down, rather than sorting each row manually.


Im unsure I need to highlight cells G1:L1 and then enter an array formula in the formula bar?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Son

Active Member
Joined
Mar 19, 2010
Messages
284
If you want to sort by row, you can use the following code and sort the existing data.

Code:
Sub SortByRow()


Dim x
For x = 1 To 120


    Range("A" & x & ":F" & x).Select
    Selection.Sort Key1:=Range("A" & x), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
        DataOption1:=xlSortNormal
Next x


    Range("a1").Select
End Sub

If you want, you could copy first the data to range G:L and then use this area for sorting.
 

thisoldman

Well-known Member
Joined
Jan 5, 2014
Messages
1,074
The array formula method.

Select G1:L1 and Ctrl+Shift+Enter the array formula,

=SMALL(A1:F1, COLUMN(A:F))

All six cells should be populated and curly brackets (braces?) should appear at the beginning and end of the formula. Then copy this line of six cells and paste downward.
 
  • Like
Reactions: Son
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,008
Messages
5,834,852
Members
430,324
Latest member
bosphoruskid

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top