I am trying to compare the numbers in the Reachability Set
column with the numbers in the same row of the Antecedent Set
column and return the common values in the corresponding cells of the Intersection Set
column.
I am trying to compare the numbers in the Reachability Set
column with the numbers in the same row of the Antecedent Set
column and return the common values in the corresponding cells of the Intersection Set
column.
In Excel 2016 (but NOT Excel 2013), you can use the following array-entered formula.
=TEXTJOIN(",",TRUE,IFERROR(1/(1/(ISNUMBER(FIND(","&TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",99)),seq_99,99))&",",","&A2&","))))*TRIM(MID(SUBSTITUTE(B2,",",REPT(" ",99)),seq_99,99)),""))
seq_99 is a Named Formula
Refers to: =IF(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))=1,1,(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))-1)*99)
To enter an array formula, after entering the formula in the cell, confirm by holding down ctrl + shift while hitting enter. If you do it correctly, Excel will place braces {...}
around the formula.