Final Class Project Instructions

We have 3 sheets of related data within a workbook:

Netflix Actors – A general list of actors and actresses who played rolls in Netflix Movies and Shows.
Netflix Movies – A list of Netflix movies that are rated PG and have a 30 minute or more runtime.
IMDB Score Category – A spreadsheet that categories IMDB scores into custom grouping.

Your Mission

Create a brand-new spreadsheet on another tab that lists all of the actors and actresses who played rolls in Netflix movies that are rated PG and have a 30min or more runtime.
Then create an additional column and bring in the IMDB custom category that the IMDB scores fall under.

Step by Step Instructions

Duplicate the Netflix Actors tab. (Right-Click on it, Move or Copy, Create a copy, OK)
Hide the original Netflix Actors Tab.
Rename your new tab. (ie: Actors-PG_30min+Movies-NF)
Find the unique Lookup value in both the Netflix Movies and new Actors-PG_30min+Movies-NF (It’s the “Movie/Show ID”)
On the Netflix Movies tab, move the “Title” column to the right of the “Movie/Show ID” (Right-click Column A, Cut, Highlight Column C, Right-Click, Insert)
In BOTH the Netflix Movies and Actors-PG_30min+Movies-NF spreadsheets, Use the TRIM formula to remove any hidden empty spaces that may be before or after the Movie/Show ID.
- Actors-PG_30min+Movies-NF: (Highlight Column C, Insert, Double-Click in cell C2, type =TRIM(B2), Press enter, drag the formula down the entire column, highlight all formulas within that column, copy, right-click in cell B2, paste as values, delete the temporary column (Column C)).
- Netflix Movies: (Highlight Column A, Insert, Double-Click in cell B2, type =TRIM(A5), Press enter, drag the formula down the entire column, highlight all formulas within that column, copy, right-click in cell A5, paste as values).
On the Netflix Movies tab, highlight/copy the headers from Column B – Column K
On the Actors-PG_30min+Movies-NF tab, click once in cell F and then press Ctrl V to paste those copied headers in.
Place a filter on all headers within the Actors-PG_30min+Movies-NF
Ensure your new columns on the Actors-PG_30min+Movies-NF tab are in General format.
Double-click in cell F2 and perform your Vlookup and press enter:
- =VLOOKUP(B2,’Netflix Movies’!A:K,2,FALSE)
Click once in cell F2, go up to the formula bar, highlight your Vlookup formula and press Ctrl C to copy it. Then press Esc.
Click once in Cell G2 and Press Ctrl V to paste the formula in.
Press the tab key to navigate to cell H2 and press Ctrl V.
Repeat these steps until you get to the last cell (O2).
Change your Column Index numbers starting in Cell G2.
- IE: The formula in cell G2 should have a Column Index of 3.
- The formula in cell H2 should have a Column Index of 4.
- all the way to Cell O2 which should have a Column Index of 11.
Highlight Cells F2 to O2
Drag the formulas down all of the columns.
While your Vlookup formulas are still highlighted, right click, copy, right-click, paste as values.
Sort A-Z in the “Title” Column.
Click inside the filter button of the “Title” Column and uncheck Select All.
Scroll down to the bottom and select #N/A.
Delete all #N/A rows.
Go back into your Title Filter and click Select All.
Hide the Netflix Movies tab.
Find the unique Lookup value in both the Actors-PG_30min+Movies-NF and new IMDB Category (It’s the “IMDB Score”)
On the Actors-PG_30min+Movies-NF tab, create a new column in column P and name it “IMDP Category”.
Apply a filter to all headers on the Actors-PG_30min+Movies-NF tab.
Go to the Actors-PG_30min+Movies-NF tab and highlight column N (“IMDP Score”).
Go to your Excel ribbon Data tab and click Text to Columns.
Click Next, Next, Finish to convert all to true numbers.
Go to the IMDB Score Category tab and highlight Column A (“IMDP Score”).
Go to your Excel ribbon Data tab and click Text to Columns.
Click Next, Next, Finish to convert all to true numbers.
Go to your Actors-PG_30min+Movies-NF tab, double click in cell P2, and type your Vlookup formula and press enter:
- =VLOOKUP(N2,’IMDB Score Category’!A:B,2,FALSE)
Drag your Vlookup Formula down the column, (Column P).

You are done! Great job!

Wesley Pesantez

--

1