Showing posts with label seo. Show all posts
Showing posts with label seo. Show all posts

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

Monday, January 17, 2011

Top 10 Excel SEO Functions - You'll Like These

Top 10 Excel SEO

Functions -

You'll Like These !



There are some functions in Excel that seem like they were custom-made just for SEO. If you are big into high-volume SEO and haven’t used many of these, you’re in for a treat.

Here are my top 10 Excel functions for SEO. I am excluding VLOOKUP, Pivot Tables, and If-Then-Else from this list because each of these is important enough to merit its own separate article. Here are my Top 10 (minus these mentioned 3):



Trim Off Extra Characters From Front and Back of a Character String

Before you perform any serious SEO work on character strings, it is always a good idea to trim off the extra and not-very-obvious spaces you may have accidently inserted at the beginning or end of a character string.

Click On Image To See Larger Version



Number of Characters in a String

This is very useful for limiting your page title and meta descriptions to correct lengths. It is also useful if you are using Twitter automation software and you are creating a bunch of tweets. Use this functionality as a quick check to keep under the 140-character Twitter limit.

Click On Image To See Larger Version



Substitute All instances of One Character In a Cell For Another

By itself, it can be useful, but it is also very useful when combined with other formulas, as we will soon see.

Click On Image To See Larger Version



Word Count in a Cell

There is no single function that directly does this, but you can overcome that with a little creativity. The following function combination will count the number of words in a cell.

Click On Image To See Larger Version


Concatenate (Combine) Contents of Multiple Cells

Concatenate has tons of uses, especially in high-volume PPC work. Here’s how to use Concatenate() to automatically create phrase match and exact match keyword phrases.

Click On Image To See Larger Version


Convert All Characters In a Cell to Lower Case

This turns all text in a cell into lower case. You might use this if you are developing a list of keywords to check with Google’s Keyword Tool.

Click On Image To See Larger Version



Create a Hyperlink With User-Friendly Anchor Text

This creates a live link on your spreadsheet so you don’t have to paste the URL into the browser address bar.


Click On Image To See Larger Version


Capitalize First Letter of Each Word In a Cell

I use this for constructing AdWords ads.

Click On Image To See Larger Version
Click On Image To See Larger Version



Calculate the Percentage of Current Month That Has Passed

This functionality is useful for creating projections for the current month.

Click On Image To See Larger Version



Convert a Number To An Integer By Truncating All Decimal Places

ROUND, ROUNDUP, and ROUNDDOWN are similar functions.

Click On Image To See Larger Version


I hope you find these helpful. If you know of any other Excel functions that would be helpful for SEO work, feel free to comment on them. Thanks.






If you would like to create a link to this blog article, here is the link to copy for your convenience: Top 10 Excel SEO Functions


Top 10 Excel SEO Functions - You'll Like These
Please post any comments you have on this article. Your opinion is highly valued!

If You Like This, Then Share It...
Top 10 Excel SEO Functions - You'll Like These Top 10 Excel SEO Functions - You'll Like These Top 10 Excel SEO Functions - You'll Like These Top 10 Excel SEO Functions - You'll Like These Top 10 Excel SEO Functions - You'll Like These Top 10 Excel SEO Functions - You'll Like These Top 10 Excel SEO Functions - You'll Like These

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic