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?
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Son

Active Member
Joined
Mar 19, 2010
Messages
281
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

Watch MrExcel Video

Forum statistics

Threads
1,122,817
Messages
5,598,275
Members
414,221
Latest member
MD1222

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