Oct 11 2022 05:42 AM
Afternoon everyone,
I'm having a slight issue with a formula. I need a table array (as shown in the attachment), with a "Yes" under any corresponding matching numbers. It works fine until it gets to double figure numbers, for example in the first row for the figure 12, it is also returning a match for both 1 and 2. Is there any adjustment I can make to the formula wherein it only matches the exact figure?
Any help is always appreciated.
Kind Regards
S
Oct 11 2022 07:36 AM
Solution@reevesgetsaround In the attached I did it both using an array formula that makes the whole table at one time and using a formula that applies to 1 cell at a time and needs to be fill down & right. Here are the formulas I used:
single cell:
=LET(in,D$22,group,$B23,
IF(ISNUMBER(in),
LET(gtemp,","&TEXTJOIN(,,IFERROR(--MID(group,SEQUENCE(LEN(group)),1),","))&",",
IF(ISNUMBER(SEARCH(","&in&",",gtemp)),"Y","")),
IF(ISNUMBER(SEARCH(in,group)),"Y","")))
whole table:
=LET(colList,$D$12:$Q$12,rowList,$B$13:$B$20,
grouplists,SCAN("",rowList,LAMBDA(p,i,","&TEXTJOIN(,,IFERROR(--MID(i,SEQUENCE(LEN(i)),1),","))&",")),
MAKEARRAY(ROWS(rowList),COLUMNS(colList),LAMBDA(r,c,
LET(in,INDEX(colList,c),IF(ISNUMBER(in),
IF(ISNUMBER(SEARCH(","&in&",",INDEX(grouplists,r))),"Y",""),
IF(ISNUMBER(SEARCH(in,INDEX(rowList,r))),"Y",""))))))
In both cases I convert the text in col B to a list of numbers found inside. In the upper that is line 3 and in the second it is line 2. And then I check if the column is a number and then search for numbers or if not then do a text search.
Oct 12 2022 08:15 AM
Oct 11 2022 07:36 AM
Solution@reevesgetsaround In the attached I did it both using an array formula that makes the whole table at one time and using a formula that applies to 1 cell at a time and needs to be fill down & right. Here are the formulas I used:
single cell:
=LET(in,D$22,group,$B23,
IF(ISNUMBER(in),
LET(gtemp,","&TEXTJOIN(,,IFERROR(--MID(group,SEQUENCE(LEN(group)),1),","))&",",
IF(ISNUMBER(SEARCH(","&in&",",gtemp)),"Y","")),
IF(ISNUMBER(SEARCH(in,group)),"Y","")))
whole table:
=LET(colList,$D$12:$Q$12,rowList,$B$13:$B$20,
grouplists,SCAN("",rowList,LAMBDA(p,i,","&TEXTJOIN(,,IFERROR(--MID(i,SEQUENCE(LEN(i)),1),","))&",")),
MAKEARRAY(ROWS(rowList),COLUMNS(colList),LAMBDA(r,c,
LET(in,INDEX(colList,c),IF(ISNUMBER(in),
IF(ISNUMBER(SEARCH(","&in&",",INDEX(grouplists,r))),"Y",""),
IF(ISNUMBER(SEARCH(in,INDEX(rowList,r))),"Y",""))))))
In both cases I convert the text in col B to a list of numbers found inside. In the upper that is line 3 and in the second it is line 2. And then I check if the column is a number and then search for numbers or if not then do a text search.