Forum Discussion
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 first range of '1s,' but I can't figure out how to continue making ranges afterward. Could you please help?
=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.
=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.
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.
