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.

Alex_Obtain

Copper Contributor

Jan 31, 2024

OliverScheurich

I... I don't have any words. It's just so amazing! First of all, it really works, and works well. I don't know many functions of your formula, so I have a good direction for learning.

A little trouble arises when the range has only one value in a single cell, but I have a very simple solution with the IFERROR function.

=IFERROR(

LET(rng;
A1:J2;
filtered;
FILTER(COLUMN(rng);INDEX(rng;2;)=1);
numbering;
HSTACK(0;DROP(SCAN(0;SEQUENCE(1;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))))));

CONCATENATE( XLOOKUP(1;A2:J2;A1:J1;"xlookup fail");" - ";XLOOKUP(1;A2:J2;A1:J1;"xlookup fail"))
)

If I can somehow thank you, just tell me. I have been working on this task for about a week.