Showing posts with label -if-then. Show all posts
Showing posts with label -if-then. Show all posts

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)