Thursday, January 27, 2011

VLOOKUP in Excel To FInd List Matches in 2 Steps and Then Remove Them

Using VLOOKUP In Excel

To Find List Matches

In 2 Steps

and Then Remove Them



If you are an Internet marketer, you already know the importance of list management skills. Nothing beats Excel as a platform to manipulate the data in your lists.

Here is a quick lesson on how to find matching records in 2 lists in just 2 steps using VLOOKUP. We will then show you how to quickly remove those matches from either of these lists, leaving you with the original list with all records that have no matches in the other list.

Finding Matches in 2 Lists Using VLOOKUP

Step 1 – Arrange the Lists Properly and Perform a VLOOKUP

Here are the 2 lists. Arrange them so that the larger list is on the left:




Now, perform your VLOOKUP. Type the formula shown below into cell D2 and copy it down to cell D9. The results of the VLOOKUP appear in cells D2 to D9. An explanation of this VLOOKUP is provided next.

Click on the Image To See Larger Version

 
Here is an explanation of each argument of the VLOOKUP function in this case:



Column D contains the results of the VLOOKUP. The actual contents of the cells in this column are the VLOOKUP formulas, not the values showing in the cells. We need to work with only the values that appear and not the formulas.


Step 2 - Copy and Sort the Values from VLOOKUP

To do this, copy the contents of the column and then paste only their values using Paste / Paste Special / Values as shown here:

Click on the Image To See Larger Version


Now that we have a column of values and not formulas, we can sort the column with the ordinary Sort function under the Data tab as follows:

Click on the Image To See Larger Version


The Matches in Both Lists

Here is the result of the Sort. Highlighted in yellow are the matches from both lists:



Removing the Matches From List 1.

Paste the matches at the bottom of List 1:




Sort List 1 to get this result:



Use an If-Then-Else statement to print any cells that do not have matches above or below them. If a cell above or below matches that cell, only a blank space (“ “) will be printed. The formula shown in cell Q4 can be copied in cells Q2 to Q13 to get the following result:

Click On Image To See Larger Version


The contents of the cells in column Q are actually formulas, not the numbers that appear in the cells that are the result of the calculation. We need to copy only those numerical values into a column so we can perform data operations on them.

Do this the same way it was done above. Copy the contents of cells Q2 to Q13 and paste them into a new column using Paste Special / Values to get the following result:

Click On Image To See Larger Version


Sorting these will give you the final List 1 (highlighted in yellow below) with all matches from List 2 removed:

Click On Image To See Larger Version


You could easily run the same removal procedure on List 2 to remove the matches from that list as well. If you did that, you would have 2 lists that had no matching records.




If you would like to create a link to this blog article, here is the link to copy for your convenience: Using VLOOKUP In Excel To Find and Remove Matches in 2 Lists

Using VLOOKUP In Excel To Remove Matches in 2 Lists
Please post any comments you have on this article. Your opinion is highly valued!

If You Like This, Then Share It...
Using VLOOKUP In Excel To Remove Matches in 2 Lists Using VLOOKUP In Excel To Remove Matches in 2 Lists Using VLOOKUP In Excel To Remove Matches in 2 Lists Using VLOOKUP In Excel To Remove Matches in 2 Lists Using VLOOKUP In Excel To Remove Matches in 2 Lists Using VLOOKUP In Excel To Remove Matches in 2 Lists Using VLOOKUP In Excel To Remove Matches in 2 Lists

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Posted by at 2 comments:
Labels: , , , , , , , , ,

Sunday, January 23, 2011

Using If-Then-Else In Excel To Remove Matches in 2 Lists

Using If-Then-Else In Excel

To Remove Matches

in 2 Lists



One thing we Internet marketing managers do is handle lots of data. List management skill is a key requirement for this job. A situation I run into quite often, particularly when I’m doing SEO work, is to locate and remove matches from 1 list that are found in a 2nd list.

For example, I have Lists 1 and 2 as shown below.

Click On Image to See Larger Version


I want to remove any records from List 1 that also appear in List 2, which would leave me with the following List 1:

Click On Image to See Larger Version


The If-Then-Else-Statement

One easy way to do this is to use the If-Then-Else statement. In my own humble opinion, the Else-Then-Else statement is the most useful bit of code available to the Excel modeler. I don’t know that I’ve ever made a detailed Excel model that didn’t make use of If-Then-Else at least once, and usually multiple times. I couldn’t live without If-Then-Else.

There are 2 main steps to removing matches in 1 list that are found in another. They are:

1) Determine what records exist in both lists
2) Remove those records from List 1


Main Step 1 – Find Matches Records That Exist In Both Lists

Here are the two lists once again:

Click On Image to See Larger Version


Here are the matches in each list highlighted with matching colors:

Click on Image To See Larger Version


Remove the duplicates in each list. Select both lists and Data / Remove Duplicates and then check both rows:

Click on Image To See Larger Version


Duplicates in the same column as an original record will be removed:


Click on Image To See Larger Version


Combine the lists. The easiest way is to copy one list and paste it right on top of the other, like so:

Click on Image To See Larger Version


Now, sort the list by Data / Sort as follows:

Click on Image To See Larger Version


The result of the sort looks like this:

Click on Image To See Larger Version


Use the If-Then-Else statement to place a “1” next to one record of each matching pair. Cell Q11 has the If-Then-Else formula =if(P11=P12,1,” ”)..

This means: If Cell P11 = Cell P2, Then Output a 1, Else don’t output anything into this cell. This If-Then-Else is shown below:
Click On Image To See Larger Version


The cells that contains the “1” and “ “ actually contain formulas. We need to replace the formulas with the cells with the contents (the “1”’s and “ “). We do that by copy all of these cells as follows:

Click on Image To See Larger Version


and then pasting them back on top of each other using Paste Special / Values as follows:

Click on Image To See Larger Version


The end result looks the same as it did before:

Click on Image To See Larger Version



Now we are trying to isolate the matches. These would be cells that have a 1 next to them. Highlight all the cells and Data / Sort as follows:

Click on Image To See Larger Version


We are sorting by the columns with the 1’s in it, as follows:

Click on Image To See Larger Version


The sort moves all records having a 1 in the right cell are moved to the top as follows:

Click on Image To See Larger Version


Main Step 2 - Remove the Matches From the Lists
Next copy the matches over, as follows:

Click on Image To See Larger Version


Now, paste these matches at the bottom of de-duped List 1:

Click on Image To See Larger Version


Sort List1 again, to get the following result:

Click on Image To See Larger Version


Use another If-Then-Else statement to place a 1 next to any match. This statement places a 1 next to a cell if it equals the cell over it or the cell under it, as follows:
Click on Image To See Larger Version


Perform the Copy / Paste Special operation again to convert the contents cells in the right column from formulas (If-Then-Else statements) to actual numbers.

Click on Image To See Larger Version


Sorting these 2 columns again to brings matching records (records with a 1 in the right column) to the top, as follows:

Click on Image To See Larger Version


Here is the result of this sort:

Click on Image To See Larger Version


Delete those records on top and here is the result and final objective: List1 with all records removed that have no matches in List2:
Click On Image To See Larger Version

You could easily run the same removal procedure on List2 to remove the matches from that list as well. If you did that, you would have 2 lists that had no matching records.




If you would like to create a link to this blog article, here is the link to copy for your convenience: Using If-Then-Else In Excel To Remove Matches in 2 Lists

Using If-Then-Else In Excel To Remove Matches in 2 Lists
Please post any comments you have on this article. Your opinion is highly valued!

If You Like This, Then Share It...
Using If-Then-Else In Excel To Remove Matches in 2 Lists Using If-Then-Else In Excel To Remove Matches in 2 Lists Using If-Then-Else In Excel To Remove Matches in 2 Lists Using If-Then-Else In Excel To Remove Matches in 2 Lists Using If-Then-Else In Excel To Remove Matches in 2 Lists Using If-Then-Else In Excel To Remove Matches in 2 Lists Using If-Then-Else In Excel To Remove Matches in 2 Lists

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Posted by at 2 comments:
Labels: , , , , , , , ,
Subscribe to: Posts (Atom)