Deliminating and Re-Combining cells

[jschena]

I'm not quite sure what I'm even asking, but I'll throw it out there anyway.

I have a spreadsheet that is exported from a piece of software. It has a column that describes the related work, labeled Tags. The tags column has each tag in it, separated by commas. I need to be able to see how many of each type of work there is for each store. (Using the stores is just an example, I am actually doing a ticketing system, much like a help-desk ticketing system). For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Ticket Number[/TD]
[TD]Tags[/TD]
[/TR]
[TR]
[TD]Petco[/TD]
[TD]1234[/TD]
[TD]cats, dogs, birds, fish[/TD]
[/TR]
[TR]
[TD]Petco[/TD]
[TD]1245[/TD]
[TD]birds, fish[/TD]
[/TR]
[TR]
[TD]Animal Lovers[/TD]
[TD]1210[/TD]
[TD]dogs[/TD]
[/TR]
[TR]
[TD]AnimalLovers[/TD]
[TD]1211[/TD]
[TD]dogs, frogs, whales, dolphins[/TD]
[/TR]
[TR]
[TD]Shelter[/TD]
[TD]1252[/TD]
[TD]cats, dolphins[/TD]
[/TR]
</tbody>[/TABLE]


So if Petco has 5 tickets that include "cats" in the tags, I need to be able to put this into a pivot table.

OUTCOME:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]cats[/TD]
[TD]dogs[/TD]
[TD]birds[/TD]
[TD]fish[/TD]
[TD]frogs[/TD]
[TD]whales[/TD]
[TD]dolphins[/TD]
[/TR]
[TR]
[TD]Petco[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Animal Lovers[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Shelter[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


***Is there a way to do this (steps?) without having to use VBA/macros?

 

[AvcGJS]

Countifs should do it combined with a wild card

eg

=COUNTIFS($A$3:$A$8,"Petco",$C$3:$C$8,"*cats*")
=COUNTIFS($A$3:$A$8,"Petco",$C$3:$C$8,"*dogs*")
=COUNTIFS($A$3:$A$8,"Petco",$C$3:$C$8,"*whales*") and so on.

 

[jschena]

Countifs should do it combined with a wild card

eg

=COUNTIFS($A$3:$A$8,"Petco",$C$3:$C$8,"*cats*")
=COUNTIFS($A$3:$A$8,"Petco",$C$3:$C$8,"*dogs*")
=COUNTIFS($A$3:$A$8,"Petco",$C$3:$C$8,"*whales*") and so on.

Hmm this would be a great solution, but there are about 30 different choices of Tags, making that include too much data manipulation