How to Count Unique Airport Codes Across Two Excel Columns
Question details
The user needs to calculate the total number of distinct airport codes recorded across two non-adjacent columns (departure and arrival) while ignoring any empty cells.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing a travel log where departure airport codes are in column G and arrival codes are in column O, and needing to extract the distinct count of all airports involved.
- Observed behavior
- Requires a formula or method to combine two separate ranges into one, remove blanks, and count only the unique text values.
Verify that your version of Excel supports modern dynamic array functions (Microsoft 365 or Excel 2021). If you are using an older version, you may need a custom add-in to achieve this result efficiently.
Use Dynamic Array Formulas (Microsoft 365 & Excel 2021)
The most efficient native method to combine ranges and count unique values using VSTACK, FILTER, UNIQUE, and COUNTA.
Functions like VSTACK allow you to append multiple non-adjacent columns into a single array in memory. By nesting this with FILTER to remove blanks and UNIQUE to extract distinct items, you can easily count the result.
Click on the cell where you want the final distinct count to appear.
Type the formula =COUNTA(UNIQUE(FILTER(VSTACK(G2:G100,O2:O100),VSTACK(G2:G100,O2:O100)<>""))). Adjust the ranges G2:G100 and O2:O100 to match your actual data rows.
Press Enter. The formula will automatically combine the two columns, remove any empty cells, find the unique airport codes, and count them.
Use a Custom Functions Excel Add-in
For older versions of Excel that do not support dynamic array functions, a custom add-in can provide a dedicated CountUnique function.
Count Unique Values Seamlessly in WPS Spreadsheet
WPS Spreadsheet provides powerful data processing capabilities, including full support for modern array formulas like UNIQUE and FILTER, making complex data analysis like counting distinct airport codes incredibly easy.
- 1. Open your travel log in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the airport codes.
- 2. Input the counting formula: In an empty cell, enter =COUNTA(UNIQUE(FILTER(VSTACK(G2:G1000,O2:O1000),VSTACK(G2:G1000,O2:O1000)<>""))).
- 3. Get the instant count: Press Enter. WPS Spreadsheet will calculate the distinct airport codes immediately.
Frequently Asked Questions
Can I count unique values across more than two columns using this method?
Yes, you can add more ranges into the VSTACK function. For example, VSTACK(A2:A100, B2:B100, C2:C100) will stack three columns vertically before filtering and finding unique values.
Why am I getting a #NAME? error when using VSTACK?
The #NAME? error occurs if your version of Excel does not support the VSTACK function. This function is only available in Microsoft 365 and Excel 2021 or later. If you are on an older version, you'll need to use alternative methods like VBA or an add-in.
How do I exclude specific text like 'N/A' instead of just blank cells?
You can modify the criteria in the FILTER function. For instance, to exclude blanks and 'N/A', use FILTER(array, (array<>"") * (array<>"N/A")). This ensures only valid airport codes are passed to the UNIQUE function.
