# Here's a fun one. Count occurences of comma separated string against another comma separated string

#### RockandGrohl

##### Well-known Member
Hello all.

Let's say I'm trying to count how many products are applicable to papers, the data I have will look like this:

 Paper Name Pickups Bridlington Press Bridlington, Scarborough, Filey Ross Gazette Ross-on-Wye, Cinderford, Hereford, Newent

And the products look like this

 Product Pickups Isle of Wight Party Whitby, Scarborough, Pickering, Malton, York, Selby, Pontefract, Doncaster, Worksop, Retford, Bridlington Somerset Sunsets Scarborough, Cinderford, Worcester

So what I'm trying to do is match up and count papers and products that have applicable pickups. The trouble is that they are both within a list, so a countif with wildcards will only work one way.

The expected result would be like this:

 Paper Name Pickups Products Bridlington Press Bridlington, Scarborough, Filey 2 Ross Gazette Ross-on-Wye, Cinderford, Hereford, Newent 0

I guess it's about matching arrays? I have a similar process in another sheet, but it does this:

1. Takes paper pickups
2. Separates them out into individual cells
3. Determines which pickups are coach pickups
5. Applies a variable to each one (Pickup 1, Pickup 2, Pickup 3) etc
6. Loads the product list and applies a countif array wildcard formula
Excel Formula:
``=COUNT(SEARCH({", Saltash,",", Plymouth,",", Blank,",", Blank,",", Blank,",", Blank,",", Blank,"},", "&I2&","))``

And from there it tells me how many times either Saltash or Plymouth appear in the pickup list for the product in I2

My question then is there an easy way to do this for my task way above, having a list of papers and their pickups, and wanting to find out how many products can fit in that group, AND THEN AS A WILDCARD, HOW MANY PRODUCTS WITH A CRITERIA, SO I CAN SEE PRODUCTS PER MONTH TOO!

Thanks.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### C Moore

##### Well-known Member
Power Query can take a table and split column by delimiter to create a row for each. Link below is to a Power BI post, but the functionality is the same.

#### knigget40

##### New Member
' Perhaps this macro will help

VBA Code:
``````Option Explicit

Public Const COL_PAPER_NAME As Long = 1 ' Paper Names in Column A
Public Const COL_PAPER_PICKUPS As Long = 2 ' Paper Pickups in Column B
Public Const COL_CALC_PRODUCTS As Long = 3 ' The calulated Products will go into column C
Public Const COL_PRODUCT_NAME As Long = 6 ' Product Names in Column F
Public Const COL_PRODUCT_PICKUPS As Long = 7 ' Product Pickups in Column G

Sub mt01()
Dim thePaperRange As Range
Dim theProductRange As Range
Dim aPaperCell As Range
Dim aProductCell As Range

' Replace CommonRange.getRealUsedRangeWithOffsets with however you want to be able to get your data ranges
' In my version of your spreadheet I put the data in columns as defined above in the constants

Dim arrayOfPapers As Variant
Dim arrayOfProducts As Variant
Dim aPaper As Variant
Dim aTrimmedPaper As Variant ' this is aPaper with any preceding or trailing spaces removed
Dim aProduct As Variant
Dim foundRange As Range
Dim countOfProducts As Long

Dim firstAddress As String ' used in "With theProductRange below in order to loop through the Product Pickups

' Loop through the Papers range one cell at a time
For Each aPaperCell In thePaperRange

' Split each of the Papers into individual pickup entries using comma as the delimiter
' Compare each Paper pickup entry with Product pickup entries to see if there is a match
countOfProducts = 0
arrayOfPapers = splitTextIntoArray(aPaperCell.Value)
For Each aPaper In arrayOfPapers
' remove any spaces from the split item
aTrimmedPaper = Trim(aPaper)

' Loop through the Product Pickups for any matches to the paper
' If a match is found then increment the count for the Paper pickups
With theProductRange
Set foundRange = .Find(aTrimmedPaper & ",", LookIn:=xlValues)
Do
countOfProducts = countOfProducts + 1
Set foundRange = .FindNext(foundRange)
End If
End With

Next aPaper

' Write the count to the spreadsheet
aPaperCell.Offset(0, COL_CALC_PRODUCTS - COL_PAPER_PICKUPS).Value = countOfProducts
Next aPaperCell

End Sub

Private Function splitTextIntoArray(inText As String) As Variant

splitTextIntoArray = Split(inText, ",")

End Function``````

Last edited by a moderator:

#### RockandGrohl

##### Well-known Member
Hi :D

I didn't use it exactly in the way above, but yes this did the trick, thank you!

#### knigget40

##### New Member
I appreciate the feedback. Thank you.

Replies
15
Views
1K

1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back