Forum Discussion
COUNTIF with CSV data
Hi,
I'm looking for a way to count the number of times word occurs when there are varying comma separated values in the cells.
A1 Apple, Red Apple
A2 Apple
A3 Red Apple, Green Apple, Apple
Referencing the word from a cell
B1 Apple C1 COUNTIF(A1:A3,B1)
The answer I need is;
Apple 3
Red Apple 2
Green Apple 1
Have been experimenting with COUNTIF and wildcards but as apple is not unique not getting the right result
Thanks
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.
5 Replies
mpearce Offering two alternative solutions. If you are on a recent version of Excel, supporting TEXTJOIN and UNIQUE, a six step approach gives you the result you asked for. But, I suspect that your real life data isn't as simple as your example. The manual approach might thus nit be a viable option. Then, Power Query might come to the rescue. The PQ result is given in I1:J4.
Attached a workbook with both options.
- Thanks Riny_van_Eekelen that does work but you are correct my dataset is, not so more complex but, is too large to do the textjoin.
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.
