I'm trying to develop a formula that cross references information between three spreadsheets and counts it if it meets the given criteria. The sheets are Genre, Movies, and Ratings. Genre consists of an itemized list of movie genres. The Movies tab is a copied CSV consisting of movie ID numbers, movie names, and the genre category the movies are given. The genre category contains between one and ten categories in a single cell separated with a vertical bar. The Ratings tab is a copied CSV consisting of user ID (a unique ID given to the consumer who rated the movie), movie ID number, and the rating for the movie. What I want to do is determine which user ID rated the most movies of a given genre and display the results in Genre column D, but I don't know how to cross reference the sheets to accomplish this. I know I need to refer to Genre!A2 for the given genre, search Movies!C:C for the genre I'm looking for, look at Movies!A:A to find the movie ID that has the genre I'm looking for, then look at Ratings!B:B for that movie ID and count which user ID appears the most. I've included a link to a shared file (if your platform will allow it).
DataTestLink
DataTestLink
