I have a huge spreadsheet that contains only 7 columns. I don't want to
change the information in the 1st and last colums. The data in the middle 5
columns are randomly entered (numbers) and I need to sort them from lowest to
highest keeping them in the rows they were entered in. The first column is
a date, all the rest are 1 or 2 digit numbers varying from 1 to 59. I only
want to change the order of each row to sort the middle 5 fields in low to
high sequence WITHOUT having to do each row separately. I already know how
to do that. I cannot find a way to conduct this operation enmass. Can
anyone help?
--
big Mike

Here's an easy formulas play which does it en-mass as required
Try it in a spare copy, check out the results delivered

The "middle" source data to be sorted row-wise is assumed in B2:F2 down
In I2: =IF(B2="","",B2+COLUMNS($A:A)/10^10)
Copy I2 across by 5 cols to M2

In N2:
=IF(ISERROR(SMALL($I2:$M2,COLUMNS($A:A))),"",INDEX ($B2:$F2,MATCH(SMALL($I2:$M2,COLUMNS($A:A)),$I2:$M 2,0)))
Copy N2 across by 5 cols to R2. Select I2:R2, copy down to the last row of
source data in cols B to F. Cols N to R will return the desired row-wise
ascending sort results. Copy cols N to R, overwrite with a paste special as
values over cols B to F. Clean up, delete cols I to R.

Success? Celebrate it, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"big_mike" wrote:
I have a huge spreadsheet that contains only 7 columns. I don't want to
change the information in the 1st and last colums. The data in the middle 5
columns are randomly entered (numbers) and I need to sort them from lowest to
highest keeping them in the rows they were entered in. The first column is
a date, all the rest are 1 or 2 digit numbers varying from 1 to 59. I only
want to change the order of each row to sort the middle 5 fields in low to
high sequence WITHOUT having to do each row separately. I already know how
to do that. I cannot find a way to conduct this operation enmass. Can
anyone help?
--
big Mike

Dear Mike

If you would like to try a macro find below.. Try using a test data..

Sub CustomSort12()

Dim lngRow As Long
Dim lngLastRow As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 2 To lngLastRow
Range("B" & lngRow & ":F" & lngRow).Sort Key1:=Range("B" & lngRow),
Orientation:=2
Next

End Sub


Incase you are new to macros set the Security level to low/medium in
(Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11.
From menu 'Insert' a module and paste the code. Save. Get back to Workbook.
Run the macro from Tools|Macro|Run <selected macro()

If this post helps click Yes
---------------
Jacob Skaria


"big_mike" wrote:

I have a huge spreadsheet that contains only 7 columns. I don't want to
change the information in the 1st and last colums. The data in the middle 5
columns are randomly entered (numbers) and I need to sort them from lowest to
highest keeping them in the rows they were entered in. The first column is
a date, all the rest are 1 or 2 digit numbers varying from 1 to 59. I only
want to change the order of each row to sort the middle 5 fields in low to
high sequence WITHOUT having to do each row separately. I already know how
to do that. I cannot find a way to conduct this operation enmass. Can
anyone help?
--
big Mike

Max,

That worked great! I can't thank you enough. You saved me hours of work!
Thanks again!
--
big Mike


"Max" wrote:

Here's an easy formulas play which does it en-mass as required
Try it in a spare copy, check out the results delivered

The "middle" source data to be sorted row-wise is assumed in B2:F2 down
In I2: =IF(B2="","",B2+COLUMNS($A:A)/10^10)
Copy I2 across by 5 cols to M2

In N2:
=IF(ISERROR(SMALL($I2:$M2,COLUMNS($A:A))),"",INDEX ($B2:$F2,MATCH(SMALL($I2:$M2,COLUMNS($A:A)),$I2:$M 2,0)))
Copy N2 across by 5 cols to R2. Select I2:R2, copy down to the last row of
source data in cols B to F. Cols N to R will return the desired row-wise
ascending sort results. Copy cols N to R, overwrite with a paste special as
values over cols B to F. Clean up, delete cols I to R.

Success? Celebrate it, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"big_mike" wrote:
I have a huge spreadsheet that contains only 7 columns. I don't want to
change the information in the 1st and last colums. The data in the middle 5
columns are randomly entered (numbers) and I need to sort them from lowest to
highest keeping them in the rows they were entered in. The first column is
a date, all the rest are 1 or 2 digit numbers varying from 1 to 59. I only
want to change the order of each row to sort the middle 5 fields in low to
high sequence WITHOUT having to do each row separately. I already know how
to do that. I cannot find a way to conduct this operation enmass. Can
anyone help?
--
big Mike

Great to hear that. You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"big_mike" wrote in message
...
Max,
That worked great! I can't thank you enough. You saved me hours of work!
Thanks again!
--
big Mike

Jacob,

I am very new at macros. Thanks for the step by step. I tried your code
and received an error message. Syntax.

Sub CustomSort12() This line was background highlighted yellow.

Dim lngRow As Long
Dim lngLastRow As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 2 To lngLastRow
Range("B" & lngRow & ":F" & lngRow).Sort Key1:=Range("B" & lngRow),
Orientation:=2 The above line was highlighted as if it
were selected.
Next

End Sub

What should I do?
--
big Mike


"Jacob Skaria" wrote:

Dear Mike

If you would like to try a macro find below.. Try using a test data..

Sub CustomSort12()

Dim lngRow As Long
Dim lngLastRow As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 2 To lngLastRow
Range("B" & lngRow & ":F" & lngRow).Sort Key1:=Range("B" & lngRow),
Orientation:=2
Next

End Sub


Incase you are new to macros set the Security level to low/medium in
(Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11.
From menu 'Insert' a module and paste the code. Save. Get back to Workbook.
Run the macro from Tools|Macro|Run <selected macro()

If this post helps click Yes
---------------
Jacob Skaria


"big_mike" wrote:

I have a huge spreadsheet that contains only 7 columns. I don't want to
change the information in the 1st and last colums. The data in the middle 5
columns are randomly entered (numbers) and I need to sort them from lowest to
highest keeping them in the rows they were entered in. The first column is
a date, all the rest are 1 or 2 digit numbers varying from 1 to 59. I only
want to change the order of each row to sort the middle 5 fields in low to
high sequence WITHOUT having to do each row separately. I already know how
to do that. I cannot find a way to conduct this operation enmass. Can
anyone help?
--
big Mike

That should be in a single line

OR try this

Sub CustomSort12()
Dim lngRow As Long
Dim lngLastRow As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 2 To lngLastRow
Range("B" & lngRow & ":F" & lngRow).Sort _
Key1:=Range("B" & lngRow), Orientation:=2
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"big_mike" wrote:

Jacob,

I am very new at macros. Thanks for the step by step. I tried your code
and received an error message. Syntax.

Sub CustomSort12() This line was background highlighted yellow.

Dim lngRow As Long
Dim lngLastRow As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 2 To lngLastRow
Range("B" & lngRow & ":F" & lngRow).Sort Key1:=Range("B" & lngRow),
Orientation:=2 The above line was highlighted as if it
were selected.
Next

End Sub

What should I do?
--
big Mike


"Jacob Skaria" wrote:

Dear Mike

If you would like to try a macro find below.. Try using a test data..

Sub CustomSort12()

Dim lngRow As Long
Dim lngLastRow As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 2 To lngLastRow
Range("B" & lngRow & ":F" & lngRow).Sort Key1:=Range("B" & lngRow),
Orientation:=2
Next

End Sub


Incase you are new to macros set the Security level to low/medium in
(Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11.
From menu 'Insert' a module and paste the code. Save. Get back to Workbook.
Run the macro from Tools|Macro|Run <selected macro()

If this post helps click Yes
---------------
Jacob Skaria


"big_mike" wrote:

I have a huge spreadsheet that contains only 7 columns. I don't want to
change the information in the 1st and last colums. The data in the middle 5
columns are randomly entered (numbers) and I need to sort them from lowest to
highest keeping them in the rows they were entered in. The first column is
a date, all the rest are 1 or 2 digit numbers varying from 1 to 59. I only
want to change the order of each row to sort the middle 5 fields in low to
high sequence WITHOUT having to do each row separately. I already know how
to do that. I cannot find a way to conduct this operation enmass. Can
anyone help?
--
big Mike

That second attempt worked great! Thanks!

"Jacob Skaria" wrote:

That should be in a single line

OR try this

Sub CustomSort12()
Dim lngRow As Long
Dim lngLastRow As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 2 To lngLastRow
Range("B" & lngRow & ":F" & lngRow).Sort _
Key1:=Range("B" & lngRow), Orientation:=2
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"big_mike" wrote:

Jacob,

I am very new at macros. Thanks for the step by step. I tried your code
and received an error message. Syntax.

Sub CustomSort12() This line was background highlighted yellow.

Dim lngRow As Long
Dim lngLastRow As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 2 To lngLastRow
Range("B" & lngRow & ":F" & lngRow).Sort Key1:=Range("B" & lngRow),
Orientation:=2 The above line was highlighted as if it
were selected.
Next

End Sub

What should I do?
--
big Mike


"Jacob Skaria" wrote:

Dear Mike

If you would like to try a macro find below.. Try using a test data..

Sub CustomSort12()

Dim lngRow As Long
Dim lngLastRow As Long
lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 2 To lngLastRow
Range("B" & lngRow & ":F" & lngRow).Sort Key1:=Range("B" & lngRow),
Orientation:=2
Next

End Sub


Incase you are new to macros set the Security level to low/medium in
(Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11.
From menu 'Insert' a module and paste the code. Save. Get back to Workbook.
Run the macro from Tools|Macro|Run <selected macro()

If this post helps click Yes
---------------
Jacob Skaria


"big_mike" wrote:

I have a huge spreadsheet that contains only 7 columns. I don't want to
change the information in the 1st and last colums. The data in the middle 5
columns are randomly entered (numbers) and I need to sort them from lowest to
highest keeping them in the rows they were entered in. The first column is
a date, all the rest are 1 or 2 digit numbers varying from 1 to 59. I only
want to change the order of each row to sort the middle 5 fields in low to
high sequence WITHOUT having to do each row separately. I already know how
to do that. I cannot find a way to conduct this operation enmass. Can
anyone help?
--
big Mike