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

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
494
Office Version
  1. 2010
Platform
  1. Windows
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 NamePickups
Bridlington PressBridlington, Scarborough, Filey
Ross GazetteRoss-on-Wye, Cinderford, Hereford, Newent

And the products look like this

ProductPickups
Isle of Wight Party
Whitby, Scarborough, Pickering, Malton, York, Selby, Pontefract, Doncaster, Worksop, Retford, Bridlington
Somerset SunsetsScarborough, 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 NamePickupsProducts
Bridlington PressBridlington, Scarborough, Filey2
Ross GazetteRoss-on-Wye, Cinderford, Hereford, Newent0


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
  4. Discards non-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.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
544
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 31, 2014
Messages
25
' 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 theEntireUsedRangeLessHeader As Range
    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
    Set theEntireUsedRangeLessHeader = CommonRange.getRealUsedRangeWithOffsets(1, 0)
    Set thePaperRange = Intersect(theEntireUsedRangeLessHeader, Columns(COL_PAPER_PICKUPS))
    Set theProductRange = Intersect(theEntireUsedRangeLessHeader, Columns(COL_PRODUCT_PICKUPS))
   
    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)
                If Not foundRange Is Nothing Then
                    firstAddress = foundRange.Address
                    Do
                        countOfProducts = countOfProducts + 1
                        Set foundRange = .FindNext(foundRange)
                    Loop While Not foundRange Is Nothing And foundRange.Address <> firstAddress
                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

Active Member
Joined
Aug 1, 2018
Messages
494
Office Version
  1. 2010
Platform
  1. Windows
Hi :D

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

Watch MrExcel Video

Forum statistics

Threads
1,118,521
Messages
5,572,625
Members
412,475
Latest member
JaredNAU
Top