Finding Differences Between Lists

by Allen Wyatt
(last updated December 7, 2018)

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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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

Opening Documents in Print Layout View

If you have a Word 2003 document that always seems to open in reading layout mode, you may want to turn that "feature" ...

Discover More

Tying Workbooks Together

If you work with multiple workbooks at the same time, you might wonder how to tie them together so they open and close ...

Discover More

Where Is that Name?

Want to easily see the location of named ranges in your worksheet? It's easy; all you need to do is use the familiar Zoom ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (menu)

Relative References within Named Ranges

Excel is usually more flexible in what you can reference in formulas than is immediately apparent. This tip examines some ...

Discover More

Viewing Formula Results

When editing information in a cell, you may need to know the result of a portion of your formula. The shortcut described ...

Discover More

Working In Feet and Inches

Your chosen occupation may require that you work with linear distances in feet and inches. Excel can do this, to a ...

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

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. Maximum image size is 6Mpixels. 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 two minus 1?

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.