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

 

[Legacy 143009]

What happens if you try like;

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

 

[Fazza]

Suggest you post some sample data that can be copied to Excel. Inputs & outputs/answer.
The existing description may be adequate, but if you think it will help get a better answer, please provide more description.
regards

 

[jahsquare]

	A	B	C	D	E	F	G	H	I	J
1	Name	2012	2013	2014	2015	2016	2017		Names	Year
2	Foo	1	1	1	2	2	0		Foo Bar	2012
3	Bar	1	1	1	1	1	1		Bif Baz	2013
4	Bif	1	1	1	1	2	0		Foo Baz	2014
5	Baz	0	1	1	1	2	1		Foo	2015
6									Bif Foo Baz	2016
7									Bar	2017
8									Bif	2012
9									Bar Foo	2013
10									Bif Bar	2014
11									Baz Bif Foo Bar	2015
12									Foo Bar Bif Baz	2016
13									Baz	2017

<tbody>
</tbody>

Here is some sample data. I1:J13 -> Table7

And an updated version of the formula for B2:G5

=COUNTIFS(INDIRECT("Table7["&$I$1&"]"),("* "&$A2&" *"),INDIRECT("Table7["&$J$1&"]"),B$1)+
COUNTIFS(INDIRECT("Table7["&$I$1&"]"),($A2&" *"),INDIRECT("Table7["&$J$1&"]"),B$1)+
COUNTIFS(INDIRECT("Table7["&$I$1&"]"),("* "&$A2),INDIRECT("Table7["&$J$1&"]"),B$1)+
COUNTIFS(INDIRECT("Table7["&$I$1&"]"),$A2,INDIRECT("Table7["&$J$1&"]"),B$1)

 

[Marcelo Branco]

Try - using your data sample in post 4.

Formula in K2 copied down
=SUMPRODUCT(--ISNUMBER(SEARCH(A$2:A$5,I2)),INDEX(B$2:G$5,0,MATCH(J2,$B$1:$G$1,0)))

Hope this helps

M.

 

[Marcelo Branco]

Or

If the range A1:G5 is formatted as a Table named Table2, try
Formula in K2 copied down
=SUMPRODUCT(--ISNUMBER(SEARCH(Table2[Name],I2)),INDEX(Table2,0,MATCH(J2&"",Table2[#Headers],0)))

M.

 

[jahsquare]

Marcelo, apologies for being unclear - B2:G5 is my expected output, what I'm trying to do is replace the current formula with something more efficient, as it takes a very long time to update.

Flashbond, I am getting a #REF error.
[here is your formula updated for the sample data I posted above]
=SUMPRODUCT(--INDIRECT("Table2["&$J$1&"]")=B$1,
INDIRECT("Table7["&$I$1&"]"="* "&$D4&" *") +
INDIRECT("Table7["&$I$1&"]"=$D4&" *") +
INDIRECT("Table7["&$I$1&"]"="* "&$D4) +
INDIRECT("Table7["&$I$1&"]"=$D4))

 

[Marcelo Branco]

Marcelo, apologies for being unclear - B2:G5 is my expected output, what I'm trying to do is replace the current formula with something more efficient, as it takes a very long time to update.

Trying to understand what you need
Are the Names in A2:A5 given or do you need a formula (or macro) to extract them of the values in I2:I13?
In other words is the below data setup given?

	A​	B​	C​	D​	E​	F​	G​
1​	Name​	2012​	2013​	2014​	2015​	2016​	2017​
2​	Foo​
3​	Bar​
4​	Biff​
5​	Baz​

If so, do you need a formula to fill B2:G5 (gray area)?

M.

 

[jahsquare]

That's correct. Currently in B2:G5, I'm using the formula I provided above:

=COUNTIFS(INDIRECT("Table7["&$I$1&"]"),("* "&$A2&" *"),INDIRECT("Table7["&$J$1&"]"),B$1)+
COUNTIFS(INDIRECT("Table7["&$I$1&"]"),($A2&" *"),INDIRECT("Table7["&$J$1&"]"),B$1)+
COUNTIFS(INDIRECT("Table7["&$I$1&"]"),("* "&$A2),INDIRECT("Table7["&$J$1&"]"),B$1)+
COUNTIFS(INDIRECT("Table7["&$I$1&"]"),$A2,INDIRECT("Table7["&$J$1&"]"),B$1)

But because my dataset is very large, this takes a very long time to complete (and occasionally crashes excel). I was hoping someone could suggest a more efficient way. Like it seems that there should be a better way for me to do exact substring searches in stead of using 4 different statements OR'd together as I'm doing now.

 

[jahsquare]

* 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.