Finding Differences Between Lists

by Allen Wyatt
(last updated March 1, 2014)

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

Printing Columns and Rows

If you want to print just the contents of a number of rows and columns, it can be challenging to get the output you want. ...

Discover More

Exact Formula Copies

When you copy a formula from one cell to another, Excel normally adjusts the cell references within the formula so they are ...

Discover More

Finding the Nth Root of a Number

Finding a square root is easy because Excel provides a worksheet function for that purpose. Finding a different root may not ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

MORE EXCELTIPS (MENU)

Counting Only Money Winners

If a series of cells contain the amount of money won by individuals, you may want to count the number of individuals who ...

Discover More

Splitting Cells by Case

Excel provides several different ways that you can split apart the contents of a cell. One way it doesn't provide is to split ...

Discover More

Generating Random Strings of Characters

If you need to generate a random sequence of characters, of a fixed length, then you'll appreciate the discussion in this ...

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:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

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.

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
Share