Counting problem

[sharshra]

I have a table with one of the field having comma separated values. I want to count for each value in that field how many items are there. I´m sure there must be a simple way to do this.

For example, consider the table below. It has name of sales person & regions they cover. I would like to have a formula to list down how many sales persons are there for each region. Note that the actual table has thousands of values. I have taken just 4 rows for illustration.

Table -

Book1
	C	D
3	Name	Region
4	James	Paris, London, Madrid
5	Roger	London, Zurich
6	Thomas	Zurich, Berlin, Paris, Warsaw
7	Anthony	Paris, Madrid
Sheet1

Output required -

Book1
	E	F
8	Region	No. of sales persons
9	Paris	3
10	London	2
11	Madrid	2
12	Zurich	2
13	Berlin	1
14	Warsaw	1
Sheet1

 

[etaf]

not sure why you quoted my entire post in your reply

 

[sharshra]

Thanks, etaf. Textjoin function is returning an error with error message saying that the text is too long. As I mentioned before, table has thousands of rows with the region field containing tens of regions.

 

[etaf]

maybe fluff solution then

 

[sharshra]

not sure why you quoted my entire post in your reply

Sorry, I clicked on reply within the post instead of replying from outside. I posted it by mistake instead of deleting the draft.

 

[etaf]

@Fluff - that formula seems to crash my version of excel

Version 365 for OSX
16.85 (24051214)

i often like to try out other solutions and see how they work, just to learn

i'm not sure i have a solution ....... other than maybe VBA , which i do not provide solutions for
and the textjoin / textsplit may have array limits or row/columns limit - dont know

1000's or rows , with 10's of regions does not sound to many - BUT as you get an error clearly is an issue

 

[Cubist]

Here's a VBA option.

Sub StoreAndCountCities()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range
    Dim dataArray() As Variant
    Dim cityDict As Object
    Dim cellValue As Variant
    Dim cities As Variant
    Dim city As Variant
    Dim i As Long
    Dim outputRow As Long
 
    Set ws = ThisWorkbook.Sheets("Sheet3") 'change sheet name as needed
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
    Set dataRange = ws.Range("D4:D" & lastRow) 'Region data starts in D4
    dataArray = dataRange.Value
 
    Set cityDict = CreateObject("Scripting.Dictionary")
 
    For i = LBound(dataArray, 1) To UBound(dataArray, 1)
        cellValue = dataArray(i, 1)
        cities = Split(cellValue, ",")
        For Each city In cities
            city = Trim(city)
            If cityDict.Exists(city) Then
                cityDict(city) = cityDict(city) + 1
            Else
                cityDict.Add city, 1
            End If
        Next city
    Next i
  
    'Output
    outputRow = 8
    Dim cityKey As Variant
    For Each cityKey In cityDict.Keys
        ws.Cells(outputRow, "E").Value = cityKey
        ws.Cells(outputRow, "F").Value = cityDict(cityKey)
        outputRow = outputRow + 1
    Next cityKey
  
End Sub

 

[sharshra]

Wow Fluff I tried your solution on the sample table & it works perfectly well However, I couldn´t understand how it works but it is doing the job I´m wondering how can you create such complex formula in no time!!!!! Kudos to you I have to spend some time to decipher your formula & understand it. Do you suggest any good references?

I noticed the following while applying the formula to the actual table. Can you please advise how these can be resolved?

1. The formula works perfectly till it encounter a blank row. When it hits a blank row, it throws an error.

2. The formula works when it is in the same worksheet as the table. If the table & formula are in different worksheets & when we give the cell reference in another worksheet, it opens up a dialog box shown below Not sure why this comes up. It is not a blocker though. I can still use it in the same sheet, but curious to know why this happens.

 

[Cubist]

With few modifications to the VBA above, the UDF can take in a chosen delimiter, and multiple ranges.

Book1
	C	D	E	F	G	H	I
1
2
3	Name	Region1		Region2		Paris	3
4	James	Paris, London, Madrid		New York,Los Angeles,Chicago		London	2
5	Roger	London, Zurich		Houston,Phoenix,Philadelphia,San Antonio		Madrid	2
6	Thomas	Zurich, Berlin, Paris, Warsaw		San Diego,Dallas,San Jose,Austin		Zurich	2
7	Anthony	Paris, Madrid		Jacksonville,San Francisco,Indianapolis,Columbus,Fort Worth		Berlin	1
8				Charlotte,Seattle,Denver,El Paso		Warsaw	1
9						New York	1
10						Los Angeles	1
11						Chicago	1
12						Houston	1
13						Phoenix	1
14						Philadelphia	1
15						San Antonio	1
16						San Diego	1
17						Dallas	1
18						San Jose	1
19						Austin	1
20						Jacksonville	1
21						San Francisco	1
22						Indianapolis	1
23						Columbus	1
24						Fort Worth	1
25						Charlotte	1
26						Seattle	1
27						Denver	1
28						El Paso	1
Sheet3

Cell Formulas
Range	Formula
H3:I28	H3	=CountDelimText(",",D4:D7,F4:F8)
Dynamic array formulas.

Function CountDelimText(delim As String, ParamArray dataRanges() As Variant) As Variant
    Dim cityDict As Object
    Dim cellValue As Variant
    Dim cities As Variant
    Dim city As Variant
    Dim i As Long, j As Long
    Dim dataArray() As Variant
    Dim resultArray() As Variant
    Dim cityKey As Variant
    Dim rowCount As Long
    Dim singleRange As Range
 
    Set cityDict = CreateObject("Scripting.Dictionary")
 
    For i = LBound(dataRanges) To UBound(dataRanges)
        Set singleRange = dataRanges(i)
        dataArray = singleRange.Value
        For j = LBound(dataArray, 1) To UBound(dataArray, 1)
            cellValue = dataArray(j, 1)
            cities = Split(cellValue, delim)
            For Each city In cities
                city = Trim(city)
                If cityDict.Exists(city) Then
                    cityDict(city) = cityDict(city) + 1
                Else
                    cityDict.Add city, 1
                End If
            Next city
        Next j
    Next i
    rowCount = cityDict.Count
    ReDim resultArray(1 To rowCount, 1 To 2)
    i = 1
    For Each cityKey In cityDict.Keys
        resultArray(i, 1) = cityKey
        resultArray(i, 2) = cityDict(cityKey)
        i = i + 1
    Next cityKey
 
    CountDelimText = resultArray
End Function

 

[Fluff]

@Fluff - that formula seems to crash my version of excel

That happens if there are blank rows in the range. This will solve that

=LET(a,UNIQUE(DROP(REDUCE("",FILTER(D4:D1000,D4:D1000<>""),LAMBDA(x,y,VSTACK(x,TRIM(TEXTSPLIT(y,,","))))),1)),HSTACK(a,COUNTIFS(D:D,"*"&a&"*")))

 

[Fluff]

If the table & formula are in different worksheets & when we give the cell reference in another worksheet, it opens up a dialog box shown below

That sounds as though you have given the wrong sheet name in the formula.

The formula works perfectly till it encounter a blank row. When it hits a blank row, it throws an error.

That is fixed with the formula above.