How to count data separated by commas

Hi guys,

I would really appreciate it if anyone could help me with this task.

I have one column of data:
product1, product6, product85
product8, product31, product1, product4, product23
....

There’s about 1000 lines of this data with different product numbers separated by comma.

Is there any easy way to count the quantity of each product and have the 2-column output like this:
Product1|478
Product2|39
Product3|143

Could please anyone help me with the best approach to accomplish this task?

Thank you in advance!

Hi
to clarify, do you want to count:

the number of product items in each row:
product1, product6, product85 = 3

the sum of the numbers attached to the word "product" in each row:
product1, product6, product85 = 92

the number of times a particular product, e.g. product6, occurs in any row in the column:
product1, product6, product85
product12, product6, product8

=> product6 = 2

Hi NickyC,

Thanks for your quick response.

I want to count this:

the number of times a particular product, e.g. product6, occurs in any row in the column:
product1, product6, product85
product12, product6, product8

=> product6 = 2

By happy coincidence I had a similar query myself once

try this macro. You must enter the input range as the range containing your list, and the output cell as the cell where you want to start the results table

Please Login or Register  to view this content.

Thanks a lot NickyC!! Works great for me!!

Wondering if Excel has any functions to accomplish the same?

Not that I'm aware of, but I'm more of a well-meaning amateur than a guru on these pages
If you already have the list of products, then it should be possible using an array formula
assuming your list of products in in range a1:a12, and your list of unique product names starts in cell D1, then enter this as an array formula and copy down to the cell adjacent to the other product names (for an array formula, copy the formula into the cell and press CONTROL+SHIFT+ENTER. a curly bracket should appear around your formula in the formula bar).

Please Login or Register  to view this content.