Find a series of equal values in a row

[fred3]

I have a row of 100 cells.
Of the 100, there are 4 cells with value 1
Of the 100, there are 96 cells with value 0
There is an adjacent row with column numbers 1-100
I want to fill 4 cells (in a row or in a column) that list the column numbers where the 4 1's reside.

What's a good formula for finding the column numbers that align with the 1 values?

 

[rahulshewale1]

hii @fred3 ,

Please upload your sample excel file with desire output by manually ,


Regard
Rahul shewale

 

[fred3]

Here it is!

 

[Naresh Yadav]

Hi,

pls see attached file

regards
Naresh

 

[rahulshewale1]

hii @fred3 ,

See if is ok ?
Cell A4 :=IFERROR(INDEX($A$2:$CV$2,AGGREGATE(15,6,(COLUMN($A$1:$CV$1)-COLUMN($A$1)+1)/($A$1:$CV$1=1),COLUMNS($A$3:A3))),"")


Cell A6:
=IFERROR(INDEX($A$2:$CV$2,AGGREGATE(15,6,(COLUMN($A$1:$CV$1)-COLUMN($A$1)+1)/($A$1:$CV$1=1),ROWS($A$3:A3))),"")

Regard
Rahul shewale

 

[bosco_yip]

Another option,

If the equal values "1" always grouped together as per your example,

you can try this simplier formula :

1] A4, copied across :

=IF(COLUMNS($A:A)<=COUNTIF($1:$1,1),INDEX($A$2:$CV$2,MATCH(1,$1:$1,0)+COLUMNS($A:A)-1),"")

2] A6, copied down :

=IF(ROWS($1:1)<=COUNTIF($1:$1,1),INDEX($A$2:$CV$2,MATCH(1,$1:$1,0)+ROWS($1:1)-1),"")

Regards
Bosco

 

[fred3]

hii @fred3 ,

See if is ok ?
Cell A4 :=IFERROR(INDEX($A$2:$CV$2,AGGREGATE(15,6,(COLUMN($A$1:$CV$1)-COLUMN($A$1)+1)/($A$1:$CV$1=1),COLUMNS($A$3:A3))),"")


Cell A6:
=IFERROR(INDEX($A$2:$CV$2,AGGREGATE(15,6,(COLUMN($A$1:$CV$1)-COLUMN($A$1)+1)/($A$1:$CV$1=1),ROWS($A$3:A3))),"")

Regard
Rahul shewale

Yes! Thanks!