Challenging Question - Troubleshooting Formula

  • Thread starter Thread starter Rachel
  • Start date Start date
R

Rachel

Hi!

I received help with worksheet formula from this
newsgroup, and the formula is quite complicated (for me).
I need help troubleshooting - it is not working quite
right...

The formula is supposed to look at a number of columns
(50) and rows (100) on one sheet, and return the 6 non-
zero values in a row, in order - on another sheet. It
works!!! - but they are not in order.

For example, row1 has
0,0,0,0,0,4,0,0,44,0,27,0,0,0,44,0,24,0,0,0,0,38,0,0,0...

(there are only 6 non-zero values in each row)

The formula that I was given: {=INDEX('Sheet1'!
$A2:$BB2,LARGE(MATCH(IF('Sheet1'!$A2:$BB2<>0,'Sheet1'!
$A2:$BB2),IF('Sheet1'!$A2:$BB2<>0,'Sheet1'!$A2:$BB2),0),
6 -(COLUMN()-COLUMN($B2))))} - (I copy this down for other
rows)

returns: 4,44,44,27,24,38

So, it works but they are not in proper order. The 3rd
value (44) should be 27, and the 4th value (27) should be
44.

Any ideas or quick fixes to the formula???

Thank you so much, if you get a chance...

Rachel
 
Hi Rachel,

What a complicated formula!!

Try using this instead ..

{=LARGE('Sheet1'!$A2:$BB2,8-COLUMN())}

Enjoy,
Ton
 
I already told her about that formula the last time but it won't do what she
wants,
your formula will return

0 4 24 27 38 44 44

not

4 44 27 44 24 38

I suggested copying the whole lot and paste special>transpose to anew sheet,
then using autofilter
and filter custom on does not equal zero, then paste unto a third sheet
using transpose would yield

4 44 27 44 24 38


taken straight from the filter while I tested it,
however she obviously thought that was too complicate/time consuming
 
Hi Peo,

I took my lead from the original formula which, array entered in Row
of another sheet, returns ..

0, 4, 44, 44, 27, 24, 38, #NUM!, #NUM!, ...

My formula, similarly array entered, returns ..

0, 4, 24, 27, 38, 44, 44, #NUM!, #NUM!, ...

Both formulae provide values in columns 2 through 7 because of th
"8-COLUMN()" construct. It's hardly difficult to adjust it t
"7-COLUMN()" for columns 1 through 6.

I haven't seen the earlier thread and without more knowledge of th
requirement it's difficult to provide what may be wanted, but I di
realise after I posted that my formula wouldn't work if some, or all
of the non-zero values were negative.

Enjoy,
Ton
 
Hi Rachel,

Assuming your first row of data is in A2:AX2:

Select your 1 by 6 cells and enter the following ARRAY formula:

=N(OFFSET(A2,0,SMALL(IF(A2:AX2>0,COLUMN(A2:AX2)),{1,2,3,4,5,6})-1,1,1))

Regards,

Daniel M.
 
=N(OFFSET(A2,0,SMALL(IF(A2:AX2>0,COLUMN(A2:AX2)),{1,2,3,4,5,6})-1,1,1))

More flexible (to relocalisation) is :
=N(OFFSET(B2,0,SMALL(IF(B2:AY2>0,COLUMN(B2:AY2)-COLUMN(B2)),{1,2,3,4,5,6}),1,1))

Regards,

Daniel M.
 
Or (to be more consistent with data in A2:AX2):
=N(OFFSET(A2,0,SMALL(IF(A2:AX2>0,COLUMN(A2:AX2)-COLUMN(A2)),{1,2,3,4,5,6}),1,1))

Regards,

Daniel M.
 
Thank you all,

None of the formulas posted seem to work. I tried copy and
paste special transpose to new sheet, but when I filter,
it filters ALL the columns according to non-zero values
found in the first column (so I have some columns that
look like 0,0,0,0,34,34). hmmm.. I wish there was an easy
way (formula) to order the non-zero results. Oh well,
Thanks for the info anyway!

Me
-----Original Message-----
I already told her about that formula the last time but it won't do what she
wants,
your formula will return

0 4 24 27 38 44 44

not

4 44 27 44 24 38

I suggested copying the whole lot and paste
Click to expand...
special>transpose to anew sheet,
 
Did you try Daniel's formula, it works the way you asked for, it will return
the values greater than zero in the same order...
Btw, is it such a hard time to filter each column by itself, first filter on
A,
copy visible cells to another sheet, select show all in A and repeat with B
and so on
 
Thank you Peo, Daniel, and Tony...

I had to modify Daniel's formula to include the sheet
name...

=N(OFFSET('Sheet1'!A2,0,SMALL(IF('Sheet1'!A2:AX2>0,COLUMN
('Sheet1'!A2:AX2)-COLUMN('Sheet1'!A2)),{1,2,3,4,5,6}),1,1))

And that seems to work fine. Thank you all again - very
much!

;)
 
Back
Top