retrieve column index...

[Bob Bedford]

Hello,

I've a table wich has 45 columns and 104 lines.
the column header are simply numbers from 1 (on B1) to 45 (on AT1).
now, on each line (from 2 to 105) I've 6 times the value "1" and once the
value "0".
The resulting array I'd like is the values where there is a 1 and the last
value where there is the 0.
I mean if the first value line has a 1 in the column K,L,M,N,O and P then
the 0 in the column B, I the resulting array must be 10,11,12,13,14,15 and
last the value 1 (as the 0 is in the column B and must be the last value).
(one value per cell, as I must export this table further).

How to do so ?

Thanks for helping.

Bob

 

[Domenic]

Try the following formulas, which need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

AV2, copied down and across to Column BA:

=INDEX($B$1:$AT$1,SMALL(IF($B2:$AT2=1,COLUMN($B$1:$AT$1)-COLUMN($B$1)+1),
COLUMNS($AV2:AV2)))

BB2, copied down:

=INDEX($B$1:$AT$1,SMALL(IF($B2:$AT2<>"",IF($B2:$AT2=0,COLUMN($B$1:$AT$1)-
COLUMN($B$1)+1)),1))

Hope this helps!