Yes, you can filter list2 based on list1 using the
VLOOKUP function in Excel. Here's how you can do it:
- Open both lists in Excel. Make sure that both lists are sorted by customer numbers.
- In a new column next to list2, enter the following formula in the first cell: =VLOOKUP(A2,list1,1,FALSE)
Note: Replace "A2" with the cell reference of the first customer number in list2. - Drag the formula down to the last cell in the column. This will populate the column with either the customer number (if the customer is active) or #N/A (if the customer is inactive).
- Select the entire table (including the new column with the VLOOKUP formula).
- Click on the "Data" tab in the ribbon, and then click on "Filter". This will add filter arrows to the top of each column.
- Click on the filter arrow in the new column with the VLOOKUP formula. Uncheck the box next to "#N/A" to hide all inactive customers.
- Now you should see only the active customers in list2. You can copy and paste this filtered list to a new sheet or document to use for your letter.