digest
Short Summary
Main recommendation: Use Text to Columns to split the comma-separated country list into cells, TRIM and MATCH with ISNUMBER to flag the matching GDP rows, then filter and format the result into a clean final GDP report. The video shows how to pull constant-2005-dollar GDP figures for selected countries, years 2000 to 2010, from a 3,000-plus-record data set without manually filtering each country.
Key Points
- The source data covers around 3,000 records across many countries and measurements, running from 1970 to 2013, and the target is only the GDP figure for chosen countries and the years 2000 to 2010.
- The country list is stored in one cell separated by commas, so it must be broken into separate cells first, since filtering each country manually would be impractical.
- MATCH with an exact match (0) and a locked lookup range returns each country's position in the list, and wrapping it in ISNUMBER produces TRUE for rows to keep.
- A filter on the TRUE values, followed by deleting unneeded year columns and copying only the visible rows, produces the final data set.
- The report is then formatted with millions-based number display, a title stating constant 2005 US dollars, borders, alternating column colors, and print setup.
Details Worth Noting
- Concrete instructions:
- Split the list: select the comma-separated cell and use Data, Text to Columns (Alt, A, E), choose Delimited, select Comma, then Finish. Do not select Space, because some country names contain spaces.
- Clean the spaces: enter =TRIM() in the cells next to the split values (Control Enter, then copy down), then Paste Special (Control Alt V) with Transpose and Values Only to move the list into a vertical column labeled "Countries."
- Match: in a new column, use =MATCH(country cell, locked country list range with F4, 0), fill down, then wrap it as =ISNUMBER(...) so it returns TRUE for matched rows.
- Filter: press Control Shift L, filter the GDP field and the ISNUMBER column to show only TRUE, then delete the columns before year 2000 and the last three columns.
- Copy the visible rows with Control Asterisk, Control C, then paste into a new sheet at A1 using Paste Options to keep source column widths.
- Display in millions: select the numbers, press Control 1, Custom, and enter #,##0,, (two commas divide by a million without changing the stored values). For the year headers, use 0 "M" in Custom.
- Insert two rows above the table and paste the title "GDP breakdown at constant 2005 prices in US dollars, all countries."
- Add a bottom border under the field names via Format Cells, Borders. Apply alternating light blue and white column fills, using Format Painter (double-click to keep it active, press Escape when done).
- Printing: Page Layout, Page Setup, Landscape, Fit to One Page. Add a custom footer with the sheet name centered so it updates automatically if the sheet is renamed.
- Caveat: the speaker notes that Print Preview may not be fully accurate, so the printout should be checked.
Bottom Line
Breaking the country list into cells, matching with MATCH and ISNUMBER, and then filtering is a practical way to extract a targeted GDP report from a large data set without manual country-by-country filtering.
