Find Common Values In ALL 5 Columns With Array Formulas

[Amlesh7400]

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 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.

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 ).

My intention is to compared all 5 columns. Can someone please correct this formula? Test file (Test_data.xls) attached) 

=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))

Regards,

Amlesh

[#2 · Accepted solution]

@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.

[#3]

@Riny_van_Eekelen 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?
Regards,

[#4]

@Amlesh7400 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".

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.

[#5]

@Riny_van_Eekelen 

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.

btw I stumbled upon a simpler formula using conditional formatting > highlight cell rules > use a formula to determine which cells to format

=COUNTIF($A$2:$E$101,A2)>4

Output is highlighted in chosen cell colour.

[#6]

@Amlesh7400 To begin with PQ, the link below could be a good starting point.

https://exceloffthegrid.com/power-query-introduction/ 

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?

[#7]

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.

[#8]

@Amlesh7400 

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))

[#9]

@Peter Bartholomew 

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?

[#10]

@Amlesh7400 

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.

[#11]

@Riny_van_Eekelen 

Hi Riny

I found the test data file you produced for Amlesh and it is almost the perfect solution for something I am trying to do, but I only have data in 3 columns

I have never used Power Query until a couple of hours ago, but have managed to reduce the table on the Data sheet to 3 columns and on the Count sheet to 5, to match my needs

I have changed the Column Names on columns c to E on the Count Sheet but cannot change the names of the Columns on the Data sheet, when I do change them I get an error message [Expression.Error]The column '5-Day' of the table wasn't found

How do I change the Column Names without getting this error?

[#12]

@Paul_Sheppard Welcome to the "World of PQ".

Changing the column headers in the Count sheet is meaningless as a Refresh will use the headers that PQ finds in the Data table.

Change the column names in the Data sheet, and make some small adjustments to the query.

After the "Promoted Headers" step you see whatever column names you have given. The next step inserts a sum of a number of columns who's names are hard-coded in the query. And in the next step these columns are reordered, again with the hard-coded column names.

This query was in fact a bit sloppy but it worked at the time. 

You can delete these last two steps and create your own Count. Select the columns you want to sum, and on the Add Column tab press this icon

PQ will automatically generate the correct code with the correct column names. Next, drag the columns in place to reorder them. Same thing het. The code is written automatically, 

See if you can get it to work.

This site will help you get to grips with PQ, by the way.

https://exceloffthegrid.com/power-query-introduction/ 

[#13]

@Riny_van_Eekelen 

Hi Riny

Thanks for the pointer, now got it to work, just wanted more meaningful headers, that other users would understand

Will have a good look at the site you recommended, in some free time during the week

Once again thanks

Paul

[#14]

Hi Riny

Sorry to be a pain

As I said above I now have the Column headings as I want them and all is working well with the original data in your file

Now that I have used real data and because the sample size is low, there is no data for one of the columns yet, which gives me an error [Expression.Error]The column '5-Day' of the table wasn't found

Can the formula be changed so that it does not give this error if a column has no entries?

[#15]

@Paul_Sheppard Can't tell. You'd need to send me your file. Upload it if you can, share a link to a file on OneDrive or similar or send it to me via a direct Message.

[#16]

@Riny_van_Eekelen Hi Riny

Direct message sent with file attached

Paul