'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Filter Excel Records by Multiple ZIP Codes Using Power Query
Question details
The user needs to return all matching Excel records from a member list based on a separate list of multiple ZIP codes, including handling duplicate entries.
- Product
- Excel
- Device & OS
- Mac
- Scenario
- Merging a member list with a separate ZIP-code list to automatically filter and extract corresponding records.
- Observed behavior
- The user wants to return all matching records (including duplicates for the same ZIP code) using Power Query, but is unable to locate the 'From Table/Range' option in Excel for Mac.
Ensure both your member list and ZIP-code list are organized without empty rows, and verify that the ZIP codes in both lists are formatted identically (e.g., both formatted as Text or both as Numbers) to ensure accurate matching.
Merge Tables using Power Query (Recommended)
Use Power Query's Inner Join feature to match and extract all related records from two tables based on a common ZIP code column.
This method is ideal for Windows users or Mac users with the latest Microsoft 365 updates that fully support Power Query. It creates a dynamic connection that can be refreshed instantly whenever your source data changes.
Select your member data range and go to Insert > Table. Name this table 'DataUpdate'. Repeat this process for your ZIP-code list and name it 'Zip'.
Click on the 'DataUpdate' table, navigate to the Data tab on the ribbon, and select 'From Table/Range' to open the Power Query Editor. Load the 'Zip' table in the exact same way.
In the Power Query Editor, go to Home > Merge Queries > Merge Queries as New. Select 'DataUpdate' as the first table and 'Zip' as the second table.
Click on the ZIP Code column in both table previews to establish the match. Under Join Kind, select 'Inner (only matching rows)' to ensure only members with matching ZIP codes are kept. Click OK.
In the newly merged table, click the expand icon in the column header for the ZIP table to display the related data. Finally, click 'Close & Load' to output the filtered results to a new Excel worksheet.
Filter Using Dynamic Array Formulas (Mac Alternative)
Since some versions of Excel for Mac do not natively support full Power Query features, use the FILTER and COUNTIF functions to achieve the same result.
Use WPS Office as a Free Alternative for Data Analysis
If you are experiencing feature limitations or compatibility issues with Excel on Mac, WPS Office provides a free, lightweight, and highly compatible alternative. Enjoy familiar spreadsheet interfaces and powerful data functions without expensive subscriptions.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your Mac or PC.
- 2. Open your Spreadsheet: Launch WPS Spreadsheet and easily open your existing .xlsx files containing the ZIP-code data without any format loss.
- 3. Apply Filtering Functions: Utilize powerful built-in functions seamlessly to manipulate, filter, and extract your data.
Frequently Asked Questions
Why is 'From Table/Range' missing in Excel for Mac?
Older versions of Excel for Mac have limited Power Query support compared to Windows. While newer Microsoft 365 versions on Mac have introduced more Power Query capabilities, users on older builds may need to rely on dynamic formulas like FILTER instead.
Will merging queries in Power Query duplicate my ZIP codes?
If your member list contains multiple people residing in the same ZIP code, performing an Inner Join in Power Query will correctly return all matching rows. It safely preserves the duplicate entries without corrupting your data structure.
How do I update the merged list when new ZIP codes are added?
Once your Power Query is set up and loaded to a worksheet, simply right-click anywhere inside the final output table and click 'Refresh'. Excel will run the query again and pull in the newest data from your source tables.
Can I filter by multiple conditions simultaneously in Power Query?
Yes. After merging the queries, you can click the filter dropdown arrows on any column header in the Power Query Editor to apply extra text, number, or logical filters before clicking 'Close & Load'.
