Thursday, March 1, 2012

yogi_Determine Unique Count Of Entities Based On Characters A to Z Only Regardless Of Case

Yogi Anand, D.Eng, P.E.                Google Spreadsheet          Post  #452
user timerecords said:
Help writing a function
So I've written a doc that has all of the top movie lists as different sheets(1001 movies to watch before you die, afi top 100, imdb top 250, etc). It is set to where a person can go in and click on each sheet and put a 1 in a cell next to the movie if you've seen it and I have data accrued from there(amount seen, amount unique seen for all users, etc).
Currently I've got each list in a different sheet and the user goes through and clicks if they've seen it. I want to have one central list with all the movie titles and all people have to do is look through it once, and click if they've seen it and it will fill out the rest of these lists.
My issue is that when i copied the lists onto the sheet the formats weren't all the same, i.e. one would say Godfather, one would day GODFATHER, one would say Godfather(1972).
My question, how do I pull out all of the unique films(each film only listed once) from all these lists and not pull out three different titles for the same film? The rest I can do.
Please help,
following is a solution to the problem