Discussion Re: Find Common Values In ALL 5 Columns With Array Formulas in Excel
https://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2324659#M98770
<P><LI-USER uid="1043406"></LI-USER> Well, the query that generates the table is in the file that is attached to my previous post. Depending on your Excel version you may have a separate Power Query tab/ribbon or you'll find the tools needed on the Data ribbon under "Get & Transform Data".</P><P>The steps applied to achieve the output aren't very complicated, that is, if you are familiar with Power Query. Otherwise you may get lost in all the options and icons.</P><P> </P>Wed, 05 May 2021 04:26:16 GMTRiny_van_Eekelen2021-05-05T04:26:16ZFind Common Values In ALL 5 Columns With Array Formulas
https://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2316305#M98473
<P>Hello, </P><P>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 be found in all columns but in different rows when eyeballing each column. My intent is to programmatically find all such values across all 5 columns because the values in each of the cells can change. Also note, all 5 columns have been sorted A-Z individually.</P><P> </P><P>I think I have got close with this formula but cannot figure out the >0,1,0 or =2,0,1 which are arrays for 3 columns which is perhaps why I am getting an error (0 value across all values ).</P><P> </P><P>My intention is to compared all 5 columns. Can someone please correct this formula? Test file (Test_data.xls) attached) </P><P> </P><P>=INDEX($A$2:$A$101,MATCH(0,COUNTIF($F$2:F2,$A$2:$A$101)+IF(IF(COUNTIF($B$2:$B$101,$A$2:$A$101)>0,1,0)+IF(COUNTIF($C$2:$C$101,$A$2:$A$101)>0,1,0)+IF(COUNTIF($D$2:$D$101,$A$2:$A$101)>0,1,0)+IF(COUNTIF($E$2:$E$101,$A$2:$A$101)>0,1,0)=2,0,1),0))</P><P> </P><P>Regards,</P><P>Amlesh</P>Mon, 03 May 2021 07:17:18 GMThttps://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2316305#M98473Amlesh74002021-05-03T07:17:18ZRe: Find Common Values In ALL 5 Columns With Array Formulas
https://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2317021#M98500
<P><LI-USER uid="1043406"></LI-USER> 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.</P>Mon, 03 May 2021 11:37:40 GMThttps://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2317021#M98500Riny_van_Eekelen2021-05-03T11:37:40ZRe: Find Common Values In ALL 5 Columns With Array Formulas
https://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2324537#M98767
<LI-USER uid="403176" login="Riny_van_Eekelen"></LI-USER> Thank you very much. if possible, can you share the Power Query so i can output a new excel file anytime the cell values change?<BR />Regards,<BR />Wed, 05 May 2021 03:48:24 GMThttps://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2324537#M98767Amlesh74002021-05-05T03:48:24ZRe: Find Common Values In ALL 5 Columns With Array Formulas
https://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2324659#M98770
<P><LI-USER uid="1043406"></LI-USER> Well, the query that generates the table is in the file that is attached to my previous post. Depending on your Excel version you may have a separate Power Query tab/ribbon or you'll find the tools needed on the Data ribbon under "Get & Transform Data".</P><P>The steps applied to achieve the output aren't very complicated, that is, if you are familiar with Power Query. Otherwise you may get lost in all the options and icons.</P><P> </P>Wed, 05 May 2021 04:26:16 GMThttps://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2324659#M98770Riny_van_Eekelen2021-05-05T04:26:16ZRe: Find Common Values In ALL 5 Columns With Array Formulas
https://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2324737#M98771
<P><LI-USER uid="403176"></LI-USER> </P><P> </P><P>I have got the latest version of Excel for 365 subscription but don't know much about Power Query so thanks for the tip to help explore to refine my skills.</P><P>btw I stumbled upon a simpler formula using conditional formatting > highlight cell rules > use a formula to determine which cells to format</P><P>=COUNTIF($A$2:$E$101,A2)>4</P><P>Output is highlighted in chosen cell colour.</P><P> </P><P> </P>Wed, 05 May 2021 04:47:47 GMThttps://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2324737#M98771Amlesh74002021-05-05T04:47:47ZRe: Find Common Values In ALL 5 Columns With Array Formulas
https://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2324744#M98772
<P><LI-USER uid="1043406"></LI-USER> To begin with PQ, the link below could be a good starting point.</P><P><A href="https://exceloffthegrid.com/power-query-introduction/" target="_self">https://exceloffthegrid.com/power-query-introduction/</A> </P><P> </P><P>As to your other question, I'm not sure what the problem is. The rule you mentioned, colours a cell if the value in A2 occurs more than 4 times in the range specified A2:E101. If that is not what you want, what is it?</P>Wed, 05 May 2021 04:59:43 GMThttps://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2324744#M98772Riny_van_Eekelen2021-05-05T04:59:43ZRe: Find Common Values In ALL 5 Columns With Array Formulas
https://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2324758#M98773
it is exactly what i wanted and i solved it using a far simpler formula for me that is, without having to use power query.Wed, 05 May 2021 05:02:26 GMThttps://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2324758#M98773Amlesh74002021-05-05T05:02:26ZRe: Find Common Values In ALL 5 Columns With Array Formulas
https://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2325381#M98787
<P><LI-USER uid="1043406"></LI-USER> </P><P>Since you are using Excel 365, you could list the codes that appear in each column using</P><LI-CODE lang="excel">= 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))</LI-CODE><P>With Insider beta channel the unwieldly unpivoting step can be hidden within a Lambda function</P><LI-CODE lang="excel">= 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)))
)</LI-CODE><P>to give</P><LI-CODE lang="excel">= LET(
distinct, UNPIVOTλ(Table1),
count, COUNTIFS(Table1, distinct),
FILTER(distinct, count=5))</LI-CODE>Wed, 05 May 2021 09:17:21 GMThttps://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2325381#M98787Peter Bartholomew2021-05-05T09:17:21ZRe: Find Common Values In ALL 5 Columns With Array Formulas
https://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2333611#M99026
<P><LI-USER uid="214174"></LI-USER> </P><P> </P><P>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?</P>Fri, 07 May 2021 00:48:36 GMThttps://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2333611#M99026Amlesh74002021-05-07T00:48:36ZRe: Find Common Values In ALL 5 Columns With Array Formulas
https://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2334530#M99046
<P><LI-USER uid="1043406"></LI-USER> </P><P>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!</P><P>I hope I have not created too much confusion; I realise that this is not what a typical Excel user expects to see!</P><P> </P><P>Notes: the open and closed circles are conditional formats designed to highlight 'count=5'.</P><P>The small filtered table uses a Lambda function to 'hide' the calculation complexity.</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 644px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/279003i65020E4CC1152BB2/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>Fri, 07 May 2021 08:40:11 GMThttps://techcommunity.microsoft.com/t5/excel/find-common-values-in-all-5-columns-with-array-formulas/m-p/2334530#M99046Peter Bartholomew2021-05-07T08:40:11Z