How to Filter Excel Data Using a City List from Another Workbook
Question details
The user needs to filter a master dataset in one workbook to keep only the records that match a specific list of values located in a different workbook.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering a large nationwide dataset to retain only records corresponding to a specific state's city list stored in a separate file.
- Observed behavior
- The goal is to cross-reference two separate workbooks and isolate the matching data records efficiently without manual sorting.
Ensure both workbooks are saved locally in a recognized Excel format (.xlsx or .csv). For best results with Power Query, format the data ranges in both files as Excel Tables by selecting your data and pressing Ctrl+T.
Filter Data Using Power Query Merge (Inner Join)
Use Power Query to import both workbooks and perform an inner join, which automatically filters the master list to keep only records present in the secondary list.
Power Query is the most robust tool for cross-referencing large datasets without using complex formulas. By establishing a matching key between two tables, you can execute an Inner Join that filters out any rows from the main dataset that do not exist in your reference list.
Open a blank Excel workbook. Go to the 'Data' tab on the ribbon, click 'Get Data' > 'From File' > 'From Workbook', and select your main United States records list. Click 'Transform Data' to load it into the Power Query Editor.
Inside the Power Query window, right-click in the Queries pane on the left, select 'New Query' > 'File' > 'Excel Workbook', and import the workbook containing the Washington State cities list.
Select your main records query from the left pane. Go to the 'Home' tab and click 'Merge Queries'.
In the Merge dialog box, select the Washington cities query from the second dropdown. Click on the 'City' column in both preview tables to establish the matching key. Change the 'Join Kind' dropdown to 'Inner (only matching rows)' and click OK.
Expand or remove the merged columns as needed using the double-arrow icon on the column header. Finally, click 'Close & Load' on the Home tab to output the filtered dataset into your Excel spreadsheet.
Filter Data Across Workbooks Easily in WPS Spreadsheet
WPS Spreadsheet provides powerful data handling tools. You can quickly cross-reference and filter datasets between different workbooks using built-in functions like MATCH or VLOOKUP without navigating complex query interfaces.
- 1. Open both workbooks: Launch WPS Spreadsheet and open both the master records workbook and the city list workbook in the same program window.
- 2. Create a helper column: In your master records sheet, insert a new helper column next to the City column and name it 'Match Status'.
- 3. Input the MATCH formula: In the first cell of the helper column, type a MATCH formula pointing to the other workbook (e.g., =MATCH(B2, '[WashingtonCities.xlsx]Sheet1'!$A:$A, 0)) and press Enter.
- 4. Apply a filter: Double-click the fill handle to copy the formula down. Go to the 'Data' tab, click 'Filter', and click the drop-down arrow on your 'Match Status' column.
- 5. Exclude errors to finalize: Uncheck '#N/A' from the filter list so only valid numbers are visible. You can now copy these filtered rows to a new worksheet as your finalized list.
Frequently Asked Questions
Can I use VLOOKUP instead of Power Query to filter data?
Yes. You can add a helper column in your main workbook using VLOOKUP to search for the city in the second workbook. Once the formula is applied, simply apply an AutoFilter to hide the error values (#N/A), leaving only the matching records visible.
Why are some exact matches not being recognized between the two workbooks?
This usually happens due to hidden trailing spaces or different text formatting (like numbers stored as text). Use the TRIM() function or Power Query's 'Trim' transformation feature to clean the data in both workbooks before attempting to match them.
Does the Advanced Filter tool work across different workbooks?
Excel's Advanced Filter can extract data to another sheet, but the criteria range must reside in the same workbook instance. Copying the criteria list (e.g., the city list) into a new, temporary sheet within your main workbook is recommended before running an Advanced Filter.
Is Power Query available in older versions of Excel?
Power Query is natively built into the Data tab (labeled as 'Get & Transform') in Excel 2016 and newer versions. For Excel 2010 and 2013, it must be downloaded and installed as a separate Microsoft add-in.
