Forum Discussion

Copper Contributor

Jan 30, 2024

Solved

Ranges from specific values

I have a specific task: I need a formula that creates ranges from cells with a '1' value. I've tried several ideas, but each time, I encounter the same problem—I can create a formula that finds the f...

excel

OliverScheurich
Jan 30, 2024
Alex_Obtain
=LET(rng,
A1:J2,
filtered,
FILTER(COLUMN(rng),INDEX(rng,2,)=1),
numbering,
HSTACK(0,DROP(SCAN(0,SEQUENCE(,COLUMNS(filtered)),LAMBDA(ini,arr,IF(INDEX(filtered,,arr+1)<>INDEX(filtered,,arr)+1,ini+1,ini))),,-1)),
BYROW(UNIQUE(TOCOL(numbering)),
LAMBDA(v,TEXTJOIN(" - ",,INDEX(INDEX(rng,1,),SMALL(IF(numbering=v,filtered),1)),INDEX(INDEX(rng,1,),LARGE(IF(numbering=v,filtered),1))))))

With Office 365 or Excel for the web you can apply this formula. The range - currently A1:J2 - can be changed according to the actual data in rows 1 and 2.

OliverScheurich

Gold Contributor

Jan 30, 2024

Alex_Obtain

=LET(rng,

A1:J2,
filtered,

FILTER(COLUMN(rng),INDEX(rng,2,)=1),
numbering,

HSTACK(0,DROP(SCAN(0,SEQUENCE(,COLUMNS(filtered)),LAMBDA(ini,arr,IF(INDEX(filtered,,arr+1)<>INDEX(filtered,,arr)+1,ini+1,ini))),,-1)),
BYROW(UNIQUE(TOCOL(numbering)),
LAMBDA(v,TEXTJOIN(" - ",,INDEX(INDEX(rng,1,),SMALL(IF(numbering=v,filtered),1)),INDEX(INDEX(rng,1,),LARGE(IF(numbering=v,filtered),1))))))

With Office 365 or Excel for the web you can apply this formula. The range - currently A1:J2 - can be changed according to the actual data in rows 1 and 2.