Looking for a specific word or phrase and counting how often it occurs

Hey there

I have a problem. I've been asked to keep a track of how many unique games are mentioned in this geeklist. I've been doing it manually, but as there are going to be close to 3000 items I am wondering if someone can help me out with a formula that does this for me. When I copy the geeklist into excel, each line pastes into one cell, so I need a formula that looks for a certain word / phrases within this jungle. Is there anyone who can help me out here?


1002. Board Game: Secret Santa added by reneald on 2014-12-09 12:45:09
1003. Board Game: Secret Santa added by Nikiloz on 2014-12-09 12:46:12
1004. Board Game: Prêt-à-Porter added by Brutilus on 2014-12-09 13:11:37
1005. Board Game: Castles of Mad King Ludwig added by kristernevin on 2014-12-09 13:31:31
1006. Board Game: Colt Express added by mgcoe on 2014-12-09 14:11:15
1007. Board Game: The Witcher Adventure Game added by Shadow_Tracker on 2014-12-09 14:11:34
1008. Board Game: Mice and Mystics added by erykine on 2014-12-09 14:40:21
1009. Board Game: Mage Wars added by Der Clown on 2014-12-09 14:40:51
1010. Family: Holidays: Christmas added by sizzling_roller on 2014-12-09 14:49:10
1011. Board Game: Secret Santa added by LovefortheGame on 2014-12-09 15:18:44


Thanks

Stefán

Originally Posted by Glenn Kennedy

Here's one way. A helper column is used to return the game names (assuming that the : before the name and the "added" after the name are ALWAYS there. Then an array formula counts ther unique values.

Array formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Thanks mate

I will give it a try in the morning.

Stefán

Desided to try it already. There seems to be some fault in the formula or I'm doing something wrong. Probably the latter, as I'm not good enough with ExCel to know what I'm looking for there.

Secret Santa 8
Secret Santa 8
Prêt-à-Porter 8
Castles of Mad King Ludwig 7
Colt Express 6
The Witcher Adventure Game 5
Mice and Mystics 4
Mage Wars 3
Holidays: Christmas 2
Secret Santa 1

These values don't fit - as every Secret Santa line should have equal values (3), and each of the other one's should be (1). I want the array formula to search cells B1:B3000.

Stefán

oh - I can see I wasn't clear in the OP. Sorry about that.

I need to know how many unique games there are mentioned in the list and how many times each game is mentioned.

Sorry about the confusion and many thanks for the help.

Stefán

wow - this work just the way I want it to. Thanks so much for the help.

Stefán