find, countif, vlookup all in one? - pls help!!

[Marcoblanc]

Hi,

hoping someone can help, driving me crazy, probably easiest if I explain it as follows:

I have the below sentences, I'd like to firstly get the total count of keyterms that exist in each line/sentence from the list of keyterms.

Sentences

banana cake, carrot cake, orange cake..
blueberry cake, shortbread biscuit, banana..
shortbread biscuit..
carrot cake, chocolate cake, carrot cake..
chocolate cake, shortbread biscuit, apple.
chocolate biscuit, carrot cake..

Secondly, I have keyterms that i'd like to get the total occurrence count in all the sentences

Keyterms

banana cake
blueberry cake
shortbread biscuit
carrot cake
chocolate cake
chocolate biscuit

Hope that makes sense, i'd expect the results to look as follows:

Sentences

2 banana cake, carrot cake, orange cake..
2 blueberry cake, shortbread biscuit, banana..
1 shortbread biscuit..
2 carrot cake, chocolate cake, carrot cake..
2 chocolate cake, shortbread biscuit, apple.
2 chocolate biscuit, carrot cake..

Keyterms

1 banana cake
1 blueberry cake
3 shortbread biscuit
4 carrot cake
2 chocolate cake
1 chocolate biscuit

Thanks in advance

Best rgds

Marco

[XOR LX]

Hi.

Assuming your Sentences and Keyterms are in A1:A6 and E1:E6 respectively, this formula in B1:

=SUMPRODUCT(0+ISNUMBER(SEARCH(", "&$E$1:$E$6&", ",", "&A1&", ")))

and this in F1:

=SUMPRODUCT(LEN(", "&$A$1:$A$6&", ")-LEN(SUBSTITUTE(", "&$A$1:$A$6&", ",", "&E1&", ","")))/LEN(", "&E1&", ")

Copy down as required.

Regards

[Marcoblanc]

thanks XOR LX, this looks great, really appreciate your time here.. Just two things. It fails when there are characters immediately after the sentence term. i.e.

shortbread biscuit.. (fails)
shortbread biscuit (works)

I used these as sentence examples, they may have no commas and be a short single sentence or could be several sentences with commas.

Similarly, on the total keyterm count, if 2 keyterms exist of the same within the 1 sentence it fails. I..e

carrot cake, chocolate cake, carrot cake.. (fails, only counts 1 carrot cake, needs to count 2)

hope this makes sense and is a fairly easy tweak..

Kind Rgds

Marc

[XOR LX]

Bit confused. Your expected result for:

carrot cake, chocolate cake, carrot cake

was 2, not 3.

Anyway, change the formula in B1 to:

=SUMPRODUCT((LEN(", "&A1&", ")-LEN(SUBSTITUTE(", "&A1&", ",", "&$E$1:$E$6&", ","")))/LEN(", "&$E$1:$E$6&", "))

As for different delimiters, that's a trickier matter. If you can't first clean up your data so that you have a consistent delimiter, then you'll have to draw up a definitive list of all possible delimiters: spaces, commas, semi-colons, etc. that may be present in a given string and I'll attempt to write a formula to account for all of these.

And I'm not sure what these double-dots at the end are supposed to be? Could they also be single-dots? Triple-dots?

Obviously the more different types of punctuation you have in there as delimiters, the more complex will be the resulting solution.

Regards

[AlKey]

Maybe this

=SUMPRODUCT(COUNTIF(A1,"*"&$C$1:$C$6&"*"))

Row\Col	A	B	C
1	banana cake, carrot cake, orange cake..	2	banana cake
2	blueberry cake, shortbread biscuit, banana..	2	blueberry cake
3	shortbread biscuit..	1	shortbread biscuit
4	carrot cake, chocolate cake, carrot cake..	2	carrot cake
5	chocolate cake, shortbread biscuit, apple.	2	chocolate cake
6	chocolate biscuit, carrot cake..	2	chocolate biscuit

[Marcoblanc]

cheers XOR LX

Sorry, my mistake here. yes it should be 3, as below. all looks great except for when it has a trailing character or no delimiter.

i.e.

carrot cake, chocolate cake, carrot cake 3
carrot cake, chocolate cake, carrot cake. 2

banana cake, carrot cake, orange cake. 2 banana cake 1
blueberry cake, shortbread biscuit, banana. 2 blueberry cake 1
shortbread biscuit 1 shortbread biscuit 3
carrot cake, chocolate cake, carrot cake 3 carrot cake 4
chocolate cake, shortbread biscuit, apple. 2 chocolate cake 2
chocolate biscuit, carrot cake 2 chocolate biscuit 1

I'll have no delimiters in come cases such as follows:

banana cake carrot cake 0

this should return:

banana cake carrot cake 2

Double dots you can ignore, just used in my example.

Kind Rgds

Marco

[XOR LX]

Ok, but you still haven't clarified a definitive list of possible delimiters.

Can I assume that it's just 3, i.e. single space, single comma or single dot?

Does that apply to the "trailing character" as well as the delimiter? Or could that character be absolutely anything?

Regards

[Marcoblanc]

Hi Alkey,

Thanks, this works great and fixes the :

banana cake carrot cake 2 (no delimiters)

However, it doesn't count the 3 occurrences:

carrot cake, chocolate cake, carrot cake 2

should be

carrot cake, chocolate cake, carrot cake 3

any ideas?

Best rgds

Marco

[Marcoblanc]

Ok, but you still haven't clarified a definitive list of possible delimiters.

Can I assume that it's just 3, i.e. single space, single comma or single dot?

Does that apply to the "trailing character" as well as the delimiter? Or could that character be absolutely anything?

Regards

yes just the 3 you've listed.

could be absolutely anything, like a new word, keyterm etc i.e.

banana cake carrot cake
banana cake carrot cake.
banana cake, carrot cake
banana cake, carrot cake.

should all return 2

[XOR LX]

Try:

=SUMPRODUCT((LEN(" "&SUBSTITUTE(SUBSTITUTE(A1,","," "),"."," ")&" ")-LEN(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(A1,","," "),"."," ")&" "," "&$D$1:$D$6&" ","")))/LEN(" "&$D$1:$D$6&" "))

Regards

[Marcoblanc]

Try:

=SUMPRODUCT((LEN(" "&SUBSTITUTE(SUBSTITUTE(A1,","," "),"."," ")&" ")-LEN(SUBSTITUTE(" "&SUBSTITUTE(SUBSTITUTE(A1,","," "),"."," ")&" "," "&$D$1:$D$6&" ","")))/LEN(" "&$D$1:$D$6&" "))

Regards

WOW . cool.

you nailed it XOR LX !

thank you VERY much, works a treat

[XOR LX]

Sure! Glad to help!

Cheers

[AlKey]

Here is another way and it doesn't really care about what delimiters are.

=SUMPRODUCT((LEN(A2)-LEN(SUBSTITUTE(A2,$C$2:$C$7,"")))/LEN($C$2:$C$7))

Row\Col	A	B	C	D	E
1	String	Count Unique	Criteria	Count of Total matched (XOR LX)	AlKey
2	banana cake, carrot cake, orange cake..banana cake, banana cake	2	banana cake	4	4
3	blueberry cake, shortbread biscuit, banana..	2	blueberry cake	2	2
4	shortbread biscuit..	1	shortbread biscuit	1	1
5	carrot cake, chocolate cake, carrot cake..	2	carrot cake	3	3
6	chocolate cake, shortbread biscuit, apple.	2	chocolate cake	2	2
7	chocolate biscuit, carrot cake..,carrot cake, carrot cake	2	chocolate biscuit	4	4

[XOR LX]

Here is another way and it doesn't really care about what delimiters are.

Perhaps in this instance you can get away with it, yes. Though for the benefit of anyone who stumbles across this post I think it's fair to point out that your construction is not, in general, advisable, due to its lack of rigour.

For example, if A2 contained the string:

"carrot cake, orange cake..banana cake, pineapple, pineapple, pineapple"

And C2:C7 contained apple, blueberry cake, shortbread biscuit, carrot cake, etc., your formula would (I imagine wrongly in this case) return 4.

Of course, you could argue that you were "simply answering the question as it was given to me", but then who's to say that the OP didn't in fact use some made-up dummy data? There are many, many instances other than my Apple/Pineapple example which will cause this construction to fail.

Regards

[AlKey]

@XOR LX

Thank you.

[XOR LX]

You're welcome, Alkey!