"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