Sorting high numbers from low numbers between two rows

[scotty]

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

 

[Leo Heuser]

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.

 

[scotty]

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]

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

 

[scotty]

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.

 

[Leo Heuser]

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.

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)

 

[Leo Heuser]

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