Forum Discussion
COUNTIF with CSV data
mpearce The PQ solution queries the blue shaded area in my original file, a named range called "Apples". Then I added a column with just numbers 1 (can be anything, as long as it's the same, so the you have a column to combine by.
The combining of columns was done by Text.Combine . More about that in the link (one of many similar links) below.
https://exceloffthegrid.com/power-query-combine-rows-into-a-single-cell/
Once you have all the apples in one column (and on one row), separated by commas, you can split the column by delimiter. Transpose the whole lot, remove the 1 at the top and clean-up (trim) the rest to get rid of leading and trailing spaces. Now you end up with a one column table with the different apples in rows. This column can be grouped applying a count. Load the end result to Excel.
I still consider myself somewhat of a beginner when it comes to PQ, so perhaps it can be done better/differently. If you have never used PQ yourself, this is probably going to take some time to grasp. Personally, I didn't find PQ straight-forward at all in the beginning. Especially not when one has to start customising M-code and when you plan to use Excel-like functions and formulae that don't exist in PQ/M. There are many PQ tutorials on the web. Google a lot and you'll find answers all the time.
Power Query looks promising I'll look into this. If you can can explain how you queried the data or point me to existing tutorial to do similar would be be appreciated.
Cheers.
mpearce The PQ solution queries the blue shaded area in my original file, a named range called "Apples". Then I added a column with just numbers 1 (can be anything, as long as it's the same, so the you have a column to combine by.
The combining of columns was done by Text.Combine . More about that in the link (one of many similar links) below.
https://exceloffthegrid.com/power-query-combine-rows-into-a-single-cell/
Once you have all the apples in one column (and on one row), separated by commas, you can split the column by delimiter. Transpose the whole lot, remove the 1 at the top and clean-up (trim) the rest to get rid of leading and trailing spaces. Now you end up with a one column table with the different apples in rows. This column can be grouped applying a count. Load the end result to Excel.
I still consider myself somewhat of a beginner when it comes to PQ, so perhaps it can be done better/differently. If you have never used PQ yourself, this is probably going to take some time to grasp. Personally, I didn't find PQ straight-forward at all in the beginning. Especially not when one has to start customising M-code and when you plan to use Excel-like functions and formulae that don't exist in PQ/M. There are many PQ tutorials on the web. Google a lot and you'll find answers all the time.
- That's what I needed thank you. I could se all the steps just needed how that manual m-code edit was implemented.
Working now thank you!