Finding Differences Between Lists

by Allen Wyatt
(last updated March 1, 2014)

1

It is not unusual for people to keep client information in Excel worksheets. If you have a worksheet that contains the names of all your clients, and another worksheet that contains the names of your active clients, you may want to use Excel's capabilities to discover who your inactive clients are.

There are several ways you can accomplish this task. The first is through the use of VLOOKUP. This worksheet function works great, provided your lists of clients are arranged in alphabetical order. One way to use the function is to add a status column to your "all clients" worksheet. First, make sure you select your active clients and name them "Active". (How you define a name for a selected range of cells is covered in other ExcelTips.) Then, in your full list of clients, add a column (named Status) to the right of your existing data. In the cells of the Status column, use the following formula:

=IF(ISNA(VLOOKUP(A2,Active,1,FALSE)),"Inactive","Active")

This formula assumes that the client's name is in column A of the current worksheet. The result of the formula is either "Active" or "Inactive," depending on whether there is a match between the name at A2 and the names in the Active list.

Once the Status column is in place, you can use the AutoFilter capability of Excel to filter your list based on the status column. You can then easily display the inactive clients, as desired.

It should be noted that while the above example uses the VLOOKUP worksheet function, you could just as easily compose other formulas that use functions such as HLOOKUP and MATCH. Which you use depends on your personal preferences and the way in which your data is laid out.

Another solution is to use a macro to compare each name on the "all clients" list with the names on the "active clients" list. If no match is found, then the name can be safely added to the "inactive clients" list. The following macro does just that:

Sub ListInactive()
    Dim cell As Range
    Dim SearchRng As Range

    Set SearchRng = Worksheets("Sheet2").Range("A:A")
    Counter = 1 'First row on Sheet3 contains headings
    For Each cell In Worksheets("Sheet1")
      .Range("A2:A1000") _
      .SpecialCells(xlCellTypeConstants)
        ID = cell 'Client ID
        NM = cell.Offset(0, 1) 'Client name
        MatchRow = 0
        On Error Resume Next
        MatchRow = WorksheetFunction.Match(ID, _
          SearchRng, 0)
        On Error GoTo 0
        If MatchRow = 0 Then
            Counter = Counter + 1
            Worksheets("Sheet3").Cells(Counter, 1) = ID
            Worksheets("Sheet3").Cells(Counter, 2) = NM
        End If
    Next cell
End Sub

The macro makes several assumptions about the data being examined. First, it assumes that the "all clients" worksheet is the first worksheet, and that the "active clients" worksheet is the second. Also, it is assumed that the third worksheet is blank and will end up containing the list of inactive clients. Further, the assumption is that column A contains a unique client ID number and column B contains the name of the client. When the macro is finished, the third worksheet contains the client numbers and names of all the inactive clients.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2053) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Understanding Discussions

Discussions in a valuable tool for workplace collaboration on the same Word document.

Discover More

Printing without Headings

One way to use heading styles is to create a story outline. When it comes time to print the story, though, you may not want ...

Discover More

Understanding and Controlling Third-Party Cookies

Cookies can be a good thing, but they can also be aggravating. Here's a high-level explanation about a specific type of ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

MORE EXCELTIPS (MENU)

Counting Consecutive Negative Numbers

If you have a range of values that can be either positive or negative, you might wonder how to determine the largest ...

Discover More

Counting Jobs Completed On a Date

When you store the date and time in a single cell, it can be a bit confusing to count how many cells contain a particular ...

Discover More

Condensing Sequential Values to a Single Row

If you have a bunch of ZIP Codes or part numbers in a list, you may want to "condense" the list so that sequential series of ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments for this tip:

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 - 7?

2014-12-04 08:57:56

Aysun Kubilay

works excellent.


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.

Links and Sharing