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