Forum Discussion
Find Common Values In ALL 5 Columns With Array Formulas
- 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.
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
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?
- Riny_van_EekelenJan 15, 2023Platinum Contributor
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/
- Paul_SheppardJan 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_SheppardJan 16, 2023Copper ContributorHi 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?