Categorizing text data

[Shadowsliver]

Hi,

I have trouble with categorizing large amounts of text data for my home economy overview.

So I have a table with text strings (column A) and numbers (column B), and I will create a table with "search words" for the different categories I want to make, similar to this example:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Text[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Category[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apple[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fruits[/TD]
[TD]Vegetables[/TD]
[TD]Berries[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]More Apple[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]Carrot[/TD]
[TD]berry[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Carrot[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Banana[/TD]
[TD]Tomato[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Blueberry[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Carrot[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Apple[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Lingonberry[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I want to do, is primarily to have a function find how many fruits there are, how many vegetables and how many berries. Like the bold summary shown below.
Most of the time it will not be an exact string match (so the function needs to find a matching fragment, "berry" in blueberry for instance).


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Text[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Category[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apple[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fruits[/TD]
[TD]Vegetables[/TD]
[TD]Berries[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]More Apple[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]Carrot[/TD]
[TD]berry[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Carrot[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Banana[/TD]
[TD]Tomato[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Blueberry[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Carrot[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Summary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Apple[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]Fruits[/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Lingonberry[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]Berries[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have struggled to find the right function to be able to use strings from one matrix to categorize strings in another matrix to the summarize, for each category, the numbers associated with the strings.

Any help would be much appreciated, thanks in advance!

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Text[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Category[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Apple[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fruits[/TD]
[TD]Vegetables[/TD]
[TD]Berries[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]More Apple[/TD]
[TD]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD]Carrot[/TD]
[TD]berry[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Carrot[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Banana[/TD]
[TD]Tomato[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Blueberry[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Carrot[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]Summary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Apple[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD]Fruits[/TD]
[TD]

18​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Lingonberry[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD]Berries[/TD]
[TD]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Vegetables[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

E7=SUMPRODUCT(SUMIF($A$2:$A$8,"*"&$F$3:$F$4&"*",$B$2:$B$8))

E8=SUMIF($A$2:$A$8,"*"&H3&"*",$B$2:$B$8)


E9=SUMPRODUCT(SUMIF($A$2:$A$8,"*"&G3:G4&"*",$B$2:$B$8))

 

[Shadowsliver]

Thanks for assisting. However, I cannot get the suggested formel to work, I get an error message about this passage: "*"&F$3$:$F$4&"*"

 

[MARZIOTULLIO]

https://dropfile.to/px2jvF4

this is the file without error

 

[Shadowsliver]

File expired

 

[MARZIOTULLIO]

https://dropfile.to/brzUA8a

new link

 

[Rick Rothstein]

https://dropfile.to/brzUA8a

new link

You might want to consider using a well-known file sharing facility such as DropBox instead of some unknown file sharing facility like the one you used. When I downloaded your file, a second file was downloaded with it which my Norton anti-virus software identified as a thread and removed it. Given that, I deleted the copy of your file as well as I don't know what the site you used may have done to it.