Here is a portion of my worksheet.


A B C D E F
1
2 1 4 20 15 56 65
3
4 2 67 45 8 3 99
5

Sometimes all 30 cells have values in them, and sometime only Colmuns ABC
and rows 1-5 have values. Rows 1,3,5 are insignificant

What I want to do is to sort the values between row 2 & 4 by putting the "6"
lowest values in row 2 and the the "6" highest values in row 4.

Can someone help me with the code on this?

Thanks

"scotty" skrev i en meddelelse
...
Here is a portion of my worksheet.


A B C D E F
1
2 1 4 20 15 56 65
3
4 2 67 45 8 3 99
5

Sometimes all 30 cells have values in them, and sometime only Colmuns ABC
and rows 1-5 have values. Rows 1,3,5 are insignificant

What I want to do is to sort the values between row 2 & 4 by putting the
"6"
lowest values in row 2 and the the "6" highest values in row 4.

Can someone help me with the code on this?

Thanks


Scotty

Maybe this approach.

Assuming your list in A1:F5 enter this
array formula in e.g. H2:

=LARGE($A$2:$F$4*{1;0;1},COLUMN()-COLUMN($H$2)+7)

Finish with <Shift<Ctrl<Enter, also if edited later.

Copy H2 to i2:M2 with the fill handle (the little square in
the lower right corner of the cell)

In H4:

=LARGE($A$2:$F$4*{1;0;1},COLUMN()-COLUMN($H$2)+1)

Finish with <Shift<Ctrl<Enter, also if edited later.

Copy H4 to i4:M4 with the fill handle (the little square in
the lower right corner of the cell)

Empty cells in second and fourth row of your original
list will display as zero in the new list, so it is assumed,
that zero and an empty cell are not the same in your
original set. Get back, if this is a problem.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

Leo.

When I put that array formula in a cell, this is shown in that cell #value!
and it doesn't go away. So I am at a stop with that. thanks however.


ClR.

Your code worked fine when all the important cells are filled with values,
however, if only 3 of the cells in each row are filled, it does sort them
right, but it duplicates them and inserts them into the cells where there
should be no numbers.

If I had this in my cells
1 3 5
2 4 6

i would get this
1 2 3 1 2 3
4 5 6 4 5 6

However, I thank you because you did get the cogs in my head turning with
your macro and i think I can get it done with a little time???

Thanks all!!

"Leo Heuser" wrote:

"scotty" skrev i en meddelelse
...
Here is a portion of my worksheet.


A B C D E F
1
2 1 4 20 15 56 65
3
4 2 67 45 8 3 99
5

Sometimes all 30 cells have values in them, and sometime only Colmuns ABC
and rows 1-5 have values. Rows 1,3,5 are insignificant

What I want to do is to sort the values between row 2 & 4 by putting the
"6"
lowest values in row 2 and the the "6" highest values in row 4.

Can someone help me with the code on this?

Thanks


Scotty

Maybe this approach.

Assuming your list in A1:F5 enter this
array formula in e.g. H2:

=LARGE($A$2:$F$4*{1;0;1},COLUMN()-COLUMN($H$2)+7)

Finish with <Shift<Ctrl<Enter, also if edited later.

Copy H2 to i2:M2 with the fill handle (the little square in
the lower right corner of the cell)

In H4:

=LARGE($A$2:$F$4*{1;0;1},COLUMN()-COLUMN($H$2)+1)

Finish with <Shift<Ctrl<Enter, also if edited later.

Copy H4 to i4:M4 with the fill handle (the little square in
the lower right corner of the cell)

Empty cells in second and fourth row of your original
list will display as zero in the new list, so it is assumed,
that zero and an empty cell are not the same in your
original set. Get back, if this is a problem.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

"scotty" skrev i en meddelelse
...
Leo.

When I put that array formula in a cell, this is shown in that cell
#value!
and it doesn't go away. So I am at a stop with that. thanks however.


You probably didn't commit the formula with <Shift<Ctrl<Enter
instead of just <Enter?

1. Enter the formula in the formula bar.
2. Press <Shift and <Ctrl and hold them while pressing <Enter

Leo Heuser

"Leo Heuser" wrote:

"scotty" skrev i en meddelelse
...
Here is a portion of my worksheet.


A B C D E F
1
2 1 4 20 15 56 65
3
4 2 67 45 8 3 99
5

Sometimes all 30 cells have values in them, and sometime only Colmuns ABC
and rows 1-5 have values. Rows 1,3,5 are insignificant

What I want to do is to sort the values between row 2 & 4 by putting the
"6"
lowest values in row 2 and the the "6" highest values in row 4.

Can someone help me with the code on this?

Thanks


Scotty

Maybe this approach.

Assuming your list in A1:F5 enter this
array formula in e.g. H2:

=LARGE($A$2:$F$4*{1;0;1},COLUMN()-COLUMN($H$2)+7)

Finish with <Shift<Ctrl<Enter, also if edited later.

Copy H2 to i2:M2 with the fill handle (the little square in
the lower right corner of the cell)

In H4:

=LARGE($A$2:$F$4*{1;0;1},COLUMN()-COLUMN($H$2)+1)

Finish with <Shift<Ctrl<Enter, also if edited later.

Copy H4 to i4:M4 with the fill handle (the little square in
the lower right corner of the cell)

Empty cells in second and fourth row of your original
list will display as zero in the new list, so it is assumed,
that zero and an empty cell are not the same in your
original set. Get back, if this is a problem.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

There are no zeros only decimal numbers in my worsheet in the rows that are
important, and some cells are blank as they are not used and I prefer them to
stay blank.. I.E A2:F2 and A4:F4. However, the code you supplied doesn't
sort right if I have only 3 cells of each row filled. I.E. A2,B2,C2(1,3,5)
and A4,B4,C4(2,4,6). In this scenario, here is how it sorts.

0 0 0 0 0 0

6 5 4 3 2 1

it shoud read

1 2 3 blank cell ,blank cell, blank cell

4 5 6 blank cell ,blank cell, blank cell

If you could take another look I'd appreciate. I am, however, trying to
solve this with macros, but in the end, I could wind up with a lot of macros
and code to do this with there being an easier way maybe.


Thanks!!!

"scotty" skrev i en meddelelse
...


"Leo Heuser" wrote:

"scotty" skrev i en meddelelse
...
Here is a portion of my worksheet.


A B C D E F
1
2 1 4 20 15 56 65
3
4 2 67 45 8 3 99
5

Sometimes all 30 cells have values in them, and sometime only Colmuns
ABC
and rows 1-5 have values. Rows 1,3,5 are insignificant

What I want to do is to sort the values between row 2 & 4 by putting
the
"6"
lowest values in row 2 and the the "6" highest values in row 4.

Can someone help me with the code on this?

Thanks


Scotty

Maybe this approach.

Assuming your list in A1:F5 enter this
array formula in e.g. H2:

=LARGE($A$2:$F$4*{1;0;1},COLUMN()-COLUMN($H$2)+7)

Finish with <Shift<Ctrl<Enter, also if edited later.

Copy H2 to i2:M2 with the fill handle (the little square in
the lower right corner of the cell)

In H4:

=LARGE($A$2:$F$4*{1;0;1},COLUMN()-COLUMN($H$2)+1)

Finish with <Shift<Ctrl<Enter, also if edited later.

Copy H4 to i4:M4 with the fill handle (the little square in
the lower right corner of the cell)

Empty cells in second and fourth row of your original
list will display as zero in the new list, so it is assumed,
that zero and an empty cell are not the same in your
original set. Get back, if this is a problem.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

There are no zeros only decimal numbers in my worsheet in the rows that
are
important, and some cells are blank as they are not used and I prefer them
to
stay blank.. I.E A2:F2 and A4:F4. However, the code you supplied
doesn't
sort right if I have only 3 cells of each row filled. I.E.
A2,B2,C2(1,3,5)
and A4,B4,C4(2,4,6). In this scenario, here is how it sorts.

0 0 0 0 0 0

6 5 4 3 2 1

it shoud read

1 2 3 blank cell ,blank cell, blank cell

4 5 6 blank cell ,blank cell, blank cell

If you could take another look I'd appreciate. I am, however, trying to
solve this with macros, but in the end, I could wind up with a lot of
macros
and code to do this with there being an easier way maybe.


Thanks!!!




Scotty

This setup seems to do the job:

Assuming your list in A1:F5 enter this formula in e.g. H2:

=IF(COLUMN()-COLUMN($H$2)+1(12-SUM(ISBLANK($A$2:$F$4)*{1;0;1}))/2,"",SMALL(($A$2:$F$2,$A$4:$F$4),COLUMN()-COLUMN($H$2)+1))

Copy H2 to i2:M2 with the fill handle (the little square in
the lower right corner of the cell)

In H4:

=IF(COLUMN()-COLUMN($H$4)+1(12-SUM(ISBLANK($A$2:$F$4)*{1;0;1}))/2,"",SMALL(($A$2:$F$2,$A$4:$F$4),COLUMN()-COLUMN($H$4)+1+(12-SUM(ISBLANK($A$2:$F$4)*{1;0;1}))/2))

Copy H4 to i4:M4 with the fill handle (the little square in
the lower right corner of the cell)


--
Best regards
Leo Heuser

Followup to newsgroup only please.

"Leo Heuser" skrev i en meddelelse
...
"scotty" skrev i en meddelelse
...


"Leo Heuser" wrote:

"scotty" skrev i en meddelelse
...
Here is a portion of my worksheet.


A B C D E F
1
2 1 4 20 15 56 65
3
4 2 67 45 8 3 99
5

Sometimes all 30 cells have values in them, and sometime only Colmuns
ABC
and rows 1-5 have values. Rows 1,3,5 are insignificant

What I want to do is to sort the values between row 2 & 4 by putting
the
"6"
lowest values in row 2 and the the "6" highest values in row 4.

Can someone help me with the code on this?

Thanks


Scotty

Maybe this approach.

Assuming your list in A1:F5 enter this
array formula in e.g. H2:

=LARGE($A$2:$F$4*{1;0;1},COLUMN()-COLUMN($H$2)+7)

Finish with <Shift<Ctrl<Enter, also if edited later.

Copy H2 to i2:M2 with the fill handle (the little square in
the lower right corner of the cell)

In H4:

=LARGE($A$2:$F$4*{1;0;1},COLUMN()-COLUMN($H$2)+1)

Finish with <Shift<Ctrl<Enter, also if edited later.

Copy H4 to i4:M4 with the fill handle (the little square in
the lower right corner of the cell)

Empty cells in second and fourth row of your original
list will display as zero in the new list, so it is assumed,
that zero and an empty cell are not the same in your
original set. Get back, if this is a problem.

--
Best regards
Leo Heuser

Followup to newsgroup only please.

There are no zeros only decimal numbers in my worsheet in the rows that
are
important, and some cells are blank as they are not used and I prefer
them
to
stay blank.. I.E A2:F2 and A4:F4. However, the code you supplied
doesn't
sort right if I have only 3 cells of each row filled. I.E.
A2,B2,C2(1,3,5)
and A4,B4,C4(2,4,6). In this scenario, here is how it sorts.

0 0 0 0 0 0

6 5 4 3 2 1

it shoud read

1 2 3 blank cell ,blank cell, blank cell

4 5 6 blank cell ,blank cell, blank cell

If you could take another look I'd appreciate. I am, however, trying to
solve this with macros, but in the end, I could wind up with a lot of
macros
and code to do this with there being an easier way maybe.


Thanks!!!




Scotty

This setup seems to do the job:

Assuming your list in A1:F5 enter this formula in e.g. H2:

=IF(COLUMN()-COLUMN($H$2)+1(12-SUM(ISBLANK($A$2:$F$4)*{1;0;1}))/2,"",SMALL(($A$2:$F$2,$A$4:$F$4),COLUMN()-COLUMN($H$2)+1))

Copy H2 to i2:M2 with the fill handle (the little square in
the lower right corner of the cell)

In H4:

=IF(COLUMN()-COLUMN($H$4)+1(12-SUM(ISBLANK($A$2:$F$4)*{1;0;1}))/2,"",SMALL(($A$2:$F$2,$A$4:$F$4),COLUMN()-COLUMN($H$4)+1+(12-SUM(ISBLANK($A$2:$F$4)*{1;0;1}))/2))

Copy H4 to i4:M4 with the fill handle (the little square in
the lower right corner of the cell)


--
Best regards
Leo Heuser

Followup to newsgroup only please.



Both formulae are array formulae to be confirmed
with <Shift<Ctrl<Enter, also if edited later.

Or confirmed with just <Enter (SUMPRODUCT
instead of SUM):

=IF(COLUMN()-COLUMN($H$2)+1(12-SUMPRODUCT(ISBLANK($A$2:$F$4)*{1;0;1}))/2,"",SMALL(($A$2:$F$2,$A$4:$F$4),COLUMN()-COLUMN($H$2)+1))

and

=IF(COLUMN()-COLUMN($H$4)+1(12-SUMPRODUCT(ISBLANK($A$2:$F$4)*{1;0;1}))/2,"",SMALL(($A$2:$F$2,$A$4:$F$4),COLUMN()-COLUMN($H$4)+1+(12-SUMPRODUCT(ISBLANK($A$2:$F$4)*{1;0;1}))/2))


Leo Heuser

Hi Scotty.........
This code is recorded and contains some stuff that you probably don't need,
no doubt.....but it seems to do what you ask and perhaps you can edit it to
trim it down........

Sub Macro1()

Range("A2:F2").Select
Selection.Copy
Range("G2").Select
ActiveSheet.Paste
Range("A4:F4").Select
Application.CutCopyMode = False
Selection.Copy
Range("G4").Select
ActiveSheet.Paste
Range("G4:L4,G2:L2").Select
Range("G2").Activate
Application.CutCopyMode = False
ActiveWorkbook.Names.Add Name:="MyTempRange", RefersToR1C1:= _
"=Sheet1!R4C7:R4C12,Sheet1!R2C7:R2C12"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=SMALL(MyTempRange,1)"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=SMALL(MyTempRange,1)"
ActiveCell.FormulaR1C1 = "=SMALL(MyTempRange,2)"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=SMALL(MyTempRange,3)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=SMALL(MyTempRange,4)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=SMALL(MyTempRange,5)"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=SMALL(MyTempRange,6)"
Range("A4").Select
ActiveCell.FormulaR1C1 = "=LARGE(MyTempRange,6)"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=LARGE(MyTempRange,5)"
Range("C4").Select
ActiveCell.FormulaR1C1 = "=LARGE(MyTempRange,4)"
Range("D4").Select
ActiveCell.FormulaR1C1 = "=LARGE(MyTempRange,3)"
Range("E4").Select
ActiveCell.FormulaR1C1 = "=LARGE(MyTempRange,2)"
Range("F4").Select
ActiveCell.FormulaR1C1 = "=LARGE(MyTempRange,1)"
Application.Goto Reference:="MyTempRange"
Range("A2:F2,A4:F4").Select
Range("A4").Activate
Selection.Copy
Range("A2:F2").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A4:F4").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("G2:L2").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("G4:L4").Select
Selection.ClearContents
ActiveWorkbook.Names("MyTempRange").Delete
End Sub

hth
Vaya con Dios,
Chuck, CABGx3




"scotty" wrote:

Here is a portion of my worksheet.


A B C D E F
1
2 1 4 20 15 56 65
3
4 2 67 45 8 3 99
5

Sometimes all 30 cells have values in them, and sometime only Colmuns ABC
and rows 1-5 have values. Rows 1,3,5 are insignificant

What I want to do is to sort the values between row 2 & 4 by putting the "6"
lowest values in row 2 and the the "6" highest values in row 4.

Can someone help me with the code on this?

Thanks