Counting substring matches based on criteria. Is there a better/faster way?

[jahsquare]

Hi, I have the following formula running in Table1 to search for a names from Table2. The formula is running in 20 columns across thousands of records, and it takes about 30 minutes to complete..

=COUNTIFS(INDIRECT("Table2["&$J$1&"]"),("* "&$D4&" *"),INDIRECT("Table2["&$L$1&"]"),J$3)+
COUNTIFS(INDIRECT("Table2["&$J$1&"]"),($D4&" *"),INDIRECT("Table2["&$L$1&"]"),J$3)+
COUNTIFS(INDIRECT("Table2["&$J$1&"]"),("* "&$D4),INDIRECT("Table2["&$L$1&"]"),J$3)+
COUNTIFS(INDIRECT("Table2["&$J$1&"]"),$D4,INDIRECT("Table2["&$L$1&"]"),J$3)

Table2 Col J is a list of strings comprising concatenated names.
Table2 Col L is a "Year" criteria (this formula runs across 20 columns / 20 years)

Each value in Table1 Col D has a single name that may be at the beginning, middle, or end of any of the strings in Table2 Col. J.

I have tried two other solutions but I can't get them to work, and i'm not sure they'd be faster anyway:

1) =COUNTIFS(INDIRECT("Table2["&$J$1&"]"),{("* "&$D4&" *"),($D4&" *"),("* "&$D4),$D4},INDIRECT("Table2["&$L$1&"]"),J$3)

with this I get an error and can't run the formula

2) =SUMPRODUCT(
(INDIRECT("Table2["&$J$1&"]")=("* "&$D4&" *")) +
INDIRECT("Table2["&$J$1&"]")=($D4&" *")) +
INDIRECT("Table2["&$J$1&"]")=("* "&$D4)) +
INDIRECT("Table2["&$J$1&"]")=($D4))) * INDIRECT("Table2["&$L$1&"]")=J$3)

This runs but the values are wrong (much lower than expected)


Any help is much appreciated. Thanks

 

[Marcelo Branco]

* Should also note that the simplified example doesn't provide that some of the names in I2:I13 might have suffices that I do NOT want to match. I.e. searching 'baz' in A2:A5 should return false for 'bazzy' in I2:I13.

Try

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Name​

[/td][td]

2012​

[/td][td]

2013​

[/td][td]

2014​

[/td][td]

2015​

[/td][td]

2016​

[/td][td]

2017​

[/td][td][/td][td]

Names​

[/td][td]

Year​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Foo​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

2​

[/td][td="bgcolor:#D9D9D9"]

2​

[/td][td="bgcolor:#D9D9D9"]

0​

[/td][td][/td][td]

Foo Bar​

[/td][td]

2012​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Bar​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td][/td][td]

Bif Baz​

[/td][td]

2013​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Bif​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

2​

[/td][td="bgcolor:#D9D9D9"]

0​

[/td][td][/td][td]

Foo Baz​

[/td][td]

2014​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Baz​

[/td][td="bgcolor:#D9D9D9"]

0​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td="bgcolor:#D9D9D9"]

2​

[/td][td="bgcolor:#D9D9D9"]

1​

[/td][td][/td][td]

Foo​

[/td][td]

2015​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

Bif Foo Baz​

[/td][td]

2016​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

Bar​

[/td][td]

2017​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

Bif​

[/td][td]

2012​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

Bar Foo​

[/td][td]

2013​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

Bif Bar​

[/td][td]

2014​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

Baz Bif Foo Bar​

[/td][td]

2015​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

Foo Bar Bif Baz​

[/td][td]

2016​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

Baz​

[/td][td]

2017​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in B2 copied across and down
=SUMPRODUCT(ISNUMBER(SEARCH(" "&$A2&" "," "&$I$2:$I$13&" "))*($J$2:$J$13=B$1))

M.

 

[jahsquare]

Formula in B2 copied across and down
=SUMPRODUCT(ISNUMBER(SEARCH(" "&$A2&" "," "&$I$2:$I$13&" "))*($J$2:$J$13=B$1))

Not sure if i'm doing something wrong but I'm getting all "0"s in the output range.

 

[jahsquare]

There must be something funny with my formatting because when I copied your input values into excel it worked. I'll work on that. Thanks for the tip!

 

[Marcelo Branco]

Not sure if i'm doing something wrong but I'm getting all "0"s in the output range.

That's why in a table the headers are strings (text)
Try this
=SUMPRODUCT(ISNUMBER(SEARCH(" "&$A2&" "," "&$I$2:$I$13&" "))*($J$2:$J$13=--B$1))

-- converts to a number

M.

 

[Fazza]

Along the lines of Marcelo's earlier question, if the input is only the source data & nothing else, then a solution can generate the list of all words in the list for the LHS and the values of all years in the list for the headers - as well as the counts.

With some VBA this could be pretty fast.

How long are the new formulas taking?

 

[jahsquare]

Along the lines of Marcelo's earlier question, if the input is only the source data & nothing else, then a solution can generate the list of all words in the list for the LHS and the values of all years in the list for the headers - as well as the counts.

With some VBA this could be pretty fast.

How long are the new formulas taking?

It seems to be about the same, really.. Takes quite awhile.

A VBA solution to generate the output table based on the input alone would be extremely helpful. Although will still need to plot years even if there is no data, so would need to set a min/max based on the input data and create columns for every year in between.

 

[Fazza]

So, the formulas are taking ~30 minutes? I'm expecting code should take ~ 1 second or less.

Sub test()


    Dim i As Long, j As Long, k As Long
    Dim lngMinYear As Long
    Dim lngMaxYear As Long
    
    Dim a As Variant
    Dim aIn As Variant
    Dim aOut As Variant
    Dim dic As Variant
    Dim wksNew As Excel.Worksheet
    
    '-----------------------------------
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    aIn = Cells.Find(What:="Names").CurrentRegion.Value2 'input data


    lngMinYear = Application.WorksheetFunction.Min(Application.Index(aIn, 0, 2))
    lngMaxYear = Application.WorksheetFunction.Max(Application.Index(aIn, 0, 2))
    
    ReDim aOut(1 To 60000, lngMinYear - 1 To lngMaxYear)
    
    Set dic = CreateObject("Scripting.Dictionary") 'for destination array row numbers stored by name
    
    For i = LBound(aIn, 1) + 1 To UBound(aIn, 1) 'loop through input data rows
        a = Split(aIn(i, 1), " ") 'Array list of names for record i
        For j = LBound(a) To UBound(a) 'Loop though names on record i
            If Not dic.exists(a(j)) Then 'if haven't already got a record number for this name
                k = k + 1 'data for this name will be in row k of output array
                dic.Add a(j), k 'store row number for this name in dictionary object
                aOut(k, lngMinYear - 1) = a(j) 'store name in first column of output array
            End If
            aOut(dic(a(j)), aIn(i, 2)) = aOut(dic(a(j)), aIn(i, 2)) + 1 'accumulate count
        Next j
    Next i
    Erase a
    Set dic = Nothing
    Set aIn = Nothing
    
    'Create output
    Set wksNew = Application.Workbooks.Add(Template:=xlWBATWorksheet).Worksheets(1)
    With wksNew
        .Range("A2").Resize(k, lngMaxYear - lngMinYear + 2).Value2 = aOut
        .Range("A1").Value2 = "Name"
        For j = lngMinYear To lngMaxYear
            .Cells(1, j - lngMinYear + 2).Value2 = j
        Next j
    End With
    Erase aOut
    Set wksNew = Nothing
    
    Application.EnableEvents = True
    
End Sub

 

[jahsquare]

Thank you, i can get it to work on the sample data but I'm having 'type mismatch' errors on my data..
on this line:

lngMinYear = Application.WorksheetFunction.Min(Application.Index(aIn, 0, 2))

I can't figure out why though - both ranges appear to be formatted the same..

 

[jahsquare]

Ok so it's pointing to that line w/Application.Index for the type mismatch, and it seems to be related to the length of the names values. When there are fewer than ~10 names it works, but if there is even one value with many names it throws the error.

Here's one of my Names values that causes an error (note you'll need to change the delimiter to " | ") :

A61M002102 | A61B00050077 | A61B0005021 | A61B0005024 | A61B00054064 | A61B0005443 | A61B00054809 | A61B00054812 | A61B00057246 | A61M002100 | G06F00193406 | G09B001900 | G16H004063 | H04N000544543 | H04N002142201 | H04N002144218 | H04N0021458 | A61M20210027 | A61M20210044 | A61M2021005 | A61M20210083 | A61M22053375 | A61M22053553 | A61M22053561 | A61M22053584 | A61M22053592 | A61M2205502 | A61M2205505 | A61M223006 | A61M223010 | A61M223018 | A61M223030 | A61M223062


I found a few references to similar issues that may be helpful to someone more knowledgeable than I..

http://dailydoseofexcel.com/archives/2013/10/11/worksheetfunction-index-limitations/
https://stackoverflow.com/questions...ile-error-type-mismatch-array-or-user-defined

 

[Fazza]

Glad it worked on the sample data.

I am just guessing how the data differs from the sample data: maybe the below code works? Maybe not.
If not, please post sample data for the the current data.

Sub test()

    Dim i As Long, j As Long, k As Long
    Dim lngMinYear As Long
    Dim lngMaxYear As Long
    
    Dim a As Variant
    Dim aIn As Variant
    Dim aOut As Variant
    Dim dic As Variant
    Dim wksNew As Excel.Worksheet
    
    '-----------------------------------
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    With Cells.Find(What:="Names").CurrentRegion
        aIn = .Value2
        lngMinYear = Application.WorksheetFunction.Min(.Offset(, 1).Resize(, 1))
        lngMaxYear = Application.WorksheetFunction.Max(.Offset(, 1).Resize(, 1))
    End With
    
    ReDim aOut(1 To 60000, lngMinYear - 1 To lngMaxYear)
    
    Set dic = CreateObject("Scripting.Dictionary") 'for destination array row numbers stored by name
    
    For i = LBound(aIn, 1) + 1 To UBound(aIn, 1) 'loop through input data rows
        a = Split(aIn(i, 1), " ") 'Array list of names for record i
        For j = LBound(a) To UBound(a) 'Loop though names on record i
            If Not dic.exists(a(j)) Then 'if haven't already got a record number for this name
                k = k + 1 'data for this name will be in row k of output array
                dic.Add a(j), k 'store row number for this name in dictionary object
                aOut(k, lngMinYear - 1) = a(j) 'store name in first column of output array
            End If
            aOut(dic(a(j)), aIn(i, 2)) = aOut(dic(a(j)), aIn(i, 2)) + 1 'accumulate count
        Next j
    Next i
    Erase a
    Set dic = Nothing
    Set aIn = Nothing
    
    'Create output
    Set wksNew = Application.Workbooks.Add(Template:=xlWBATWorksheet).Worksheets(1)
    With wksNew
        .Range("A2").Resize(k, lngMaxYear - lngMinYear + 2).Value2 = aOut
        .Range("A1").Value2 = "Name"
        For j = lngMinYear To lngMaxYear
            .Cells(1, j - lngMinYear + 2).Value2 = j
        Next j
    End With
    Erase aOut
    Set wksNew = Nothing
    
    Application.EnableEvents = True
    
End Sub