Forum Discussion
Solved
Find Common Values In ALL 5 Columns With Array Formulas
Hello, I have test data of 101 rows containing 3 or 4 Alphabet symbols in 5 columns. Some of these 3 or 4 letter symbols can be found across all 5 columns though not in the same row e.g. "BDRY" can...
Amlesh7400 Perhaps easiest with Power Query. In the attached workbook you'll find a sheet called "Count". I trust you can do your analysis from there.
Since you are using Excel 365, you could list the codes that appear in each column using
= LET(
n, ROWS(Table1),
k, SEQUENCE(5*n,,0),
c, 1+MOD(k,5),
r, 1+QUOTIENT(k,5),
unpivoted, INDEX(Table1,r,c),
distinct, SORT(UNIQUE(unpivoted)),
FILTER(distinct, COUNTIFS(Table1,distinct)=5))With Insider beta channel the unwieldly unpivoting step can be hidden within a Lambda function
= LAMBDA(tbl,
LET(
n, ROWS(Table1),
k, SEQUENCE(5*n,,0),
c, 1+MOD(k,5),
r, 1+QUOTIENT(k,5),
unpivotted, INDEX(Table1,r,c),
SORT(UNIQUE(unpivotted)))
)to give
= LET(
distinct, UNPIVOTλ(Table1),
count, COUNTIFS(Table1, distinct),
FILTER(distinct, count=5))
Thank You. My skills in Excel are limited to using basic formulas so would like to know whether I have to input this "policy" (looks like AWS type policy to me) in power query? or is the "LET" formula to be created?