Forum Discussion
Find Common Values In ALL 5 Columns With Array Formulas
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?
The formulas are straightforward worksheet formulas (OK, perhaps not so straightforward). The LET function, and now the LAMBDA function (available within the beta channel), are somewhat 'work in progress' that is building towards Excel as a full-blown software development platform!
I hope I have not created too much confusion; I realise that this is not what a typical Excel user expects to see!
Notes: the open and closed circles are conditional formats designed to highlight 'count=5'.
The small filtered table uses a Lambda function to 'hide' the calculation complexity.