Excel.Tips.Net ExcelTips (Menu Interface)

Finding Differences Between Lists

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:


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") _
        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.

Related Tips:

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!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 4+5 (To prevent automated submissions and spam.)
          Commenting Terms

Comments for this tip:

Aysun Kubilay    04 Dec 2014, 08:57
works excellent.

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2015 Sharon Parq Associates, Inc.