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

Automatically Converting to GMT

You know what time it is, right? (Quick—look at your watch.) What if you want to know what time it is in Greenwich, ...

Discover More

Adding Graphics to a Header or Footer

Excel makes it easy to add graphics to a header or footer. Here's the steps to make it happen.

Discover More

Embedding Your Phone Number in a Document

One way you can designate your responsibility for a document is to add your phone number to it. There is no need to add ...

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)

Finding the Smallest Even Value

When processing data in a worksheet, you may have a need to know what the smallest (lowest) even value in a range is. You ...

Discover More

Counting Unique Values

Need to know a count of unique values in a range of cells? There are a couple of ways you can tackle the problem.

Discover More

Calculating the Distance between Points

Want to figure out how far it is between two points on the globe? If you know the points by latitude and longitude, you ...

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 six minus 3?

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.