How to I filter one list based on another list?
 

I have one customer-list showing which of our customers have been active the
last year (list1), and one list showing all of our customers and their
adresses (list2). I want to send out a letter to all of the customers who
have been active the last year.

Both lists are sorted by customer-numbers. So list2 has the whole range from
100001 to 117950, and list1 shows approximately 4300 of these customers have
been active in 2008 or 2009. I don't want to waste money on sending out
letters to all the inactive customers, so..

Is there any way for me to sort list2, based on customer numbers, so that it
only shows the customers present in list1?

Answer: How to I filter one list based on another list?
 

Yes, you can filter list2 based on list1 using the VLOOKUP function in Excel. Here's how you can do it:

1. Open both lists in Excel. Make sure that both lists are sorted by customer numbers.
2. 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.
3. 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).
4. Select the entire table (including the new column with the VLOOKUP formula).
5. Click on the "Data" tab in the ribbon, and then click on "Filter". This will add filter arrows to the top of each column.
6. 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.
7. 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.

How to I filter one list based on another list?
 

I'd insert another column to show an indicator if the value appears on the
current customer list.

I'd use a formula like:

=isnumber(match(a2,sheet2!a:a,0))

True means that there is a match.

False means that there is no match.

Then either sort or filter or .... by this column.

Erlend wrote:

I have one customer-list showing which of our customers have been active the
last year (list1), and one list showing all of our customers and their
adresses (list2). I want to send out a letter to all of the customers who
have been active the last year.

Both lists are sorted by customer-numbers. So list2 has the whole range from
100001 to 117950, and list1 shows approximately 4300 of these customers have
been active in 2008 or 2009. I don't want to waste money on sending out
letters to all the inactive customers, so..

Is there any way for me to sort list2, based on customer numbers, so that it
only shows the customers present in list1?

--

Dave Peterson

How to I filter one list based on another list?
 

This worked just as it should! Thanks a lot for the tip! :)

"Dave Peterson" wrote:

I'd insert another column to show an indicator if the value appears on the
current customer list.

I'd use a formula like:

=isnumber(match(a2,sheet2!a:a,0))

True means that there is a match.

False means that there is no match.

Then either sort or filter or .... by this column.

Erlend wrote:

I have one customer-list showing which of our customers have been active the
last year (list1), and one list showing all of our customers and their
adresses (list2). I want to send out a letter to all of the customers who
have been active the last year.

Both lists are sorted by customer-numbers. So list2 has the whole range from
100001 to 117950, and list1 shows approximately 4300 of these customers have
been active in 2008 or 2009. I don't want to waste money on sending out
letters to all the inactive customers, so..

Is there any way for me to sort list2, based on customer numbers, so that it
only shows the customers present in list1?

--

Dave Peterson