Forum Discussion
Amlesh7400
May 03, 2021Copper Contributor
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...
- May 03, 2021
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.
Paul_Sheppard
Jan 15, 2023Copper Contributor
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
Paul_Sheppard
Jan 16, 2023Copper Contributor
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?
- Riny_van_EekelenJan 16, 2023Platinum Contributor
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.
- Paul_SheppardJan 16, 2023Copper Contributor