Array? Put values from random columns into 4

W

willwonka

I have a worksheet that may have a "1" in it from Columns K thru BB.
There will only be 4 "1"s.

What I am looking for is a formula to put find those values and put
them in colums G thru J.

In other words...

The first row has 1s in them in column S,U,Z and AN.

I was thinking there was some kind of array formula that can search
columns K thru BB.
 
B

Bernie Deitrick

Paul,

No array needed.

In cell G1, enter the formula

=MATCH(1,OFFSET($A$1,0,F1,1,255-F1),FALSE)+F1

and copy it to H1:J1 Make sure that F1 is blank or has a 0 entered into it.

Then in cell G2, enter the formula

=INDEX(2:2,G$1)

and copy to H2:J2. Then copy G2:J2 as far down columns G:J as you have data in columns K:BB

HTH,
Bernie
MS Excel MVP
 
B

Bernie Deitrick

Paul,

I don't get that error because I use Tools / Options Calculation tab, Iterations checked, allow 1
iteration. But to get around it, you can use this in G1:

=MATCH(1,OFFSET($K$1,0,F1,1,200-F1),FALSE)+F1

copied to H1:J1

then this in G2, copied to H2:J2

=OFFSET($A2,0,G$1+COLUMN($I$1))

HTH,
Bernie
MS Excel MVP
 
W

willwonka

Thanks... got it to work..


Paul,

I don't get that error because I use Tools / Options Calculation tab, Iterations checked, allow 1
iteration. But to get around it, you can use this in G1:

=MATCH(1,OFFSET($K$1,0,F1,1,200-F1),FALSE)+F1

copied to H1:J1

then this in G2, copied to H2:J2

=OFFSET($A2,0,G$1+COLUMN($I$1))

HTH,
Bernie
MS Excel MVP







- Show quoted text -
Click to expand...
 
Top