List names not on a list via VBA

Posted on 2012-08-12
Last Modified: 2012-09-21

I have a list of names on a tab called 'Staff'. The list starts from A3 downwards.  The list goes down from A3 and varies daily.

I would like to do the following.

Look at each name on the current list and IF the name does not appear anywhere in column E of a data tab called Data copy the name to the Summary Tab into Cell F39, F40, F41 etc.. depending upon how many names are picked up.

Any help would be greatly appreciated ;)
Question by:gisvpn
    LVL 5

    Expert Comment

    You can 1st have a check if your names are present in column E
    In let's say sheet Staff column B row 3 (or anywhere else ...) type:
    This will check the presence in sheet data column E of name in cell A3, if present, then, B3 = A3
    Extend the formula down to the same quantity of items in your list in column A
    So you get a list of the names that are not present in the Data list.
    Then you would need to create a short macro to copy/paste and sort this date in sheet Summary cell F39. It could be:
    Sub Test()

    sheets("Staff").Range("B3:B52").select ' for 50 names
    sheets("Summary").range("F59").PasteSpecial xlPasteValues
    Selection.Sort Key1:=Worksheets("Summary").Range("F59")

    End Sub

    ...Or something that looks like that. I didn't test the code but it should work.
    Any problem, just google a bit and you'll find the error

    To run the macro, just assign a macro to a shape (Insert>Shape then right-click>Assign Macro) and click on your shape anytime you want to update your list.
    LVL 8

    Accepted Solution

    Try this:

    Option Explicit
    Sub TranserNonExistent()
        Dim wb As Workbook
        Dim wsStaff As Worksheet
        Dim wsSummary As Worksheet
        Dim wsData As Worksheet
        Dim nameList As Range
        Dim cell As Range
        Dim i As Long
        Dim notFound() As String
        Set wb = ThisWorkbook
        Set wsStaff = wb.Worksheets("Staff")
        Set wsSummary = wb.Worksheets("Summary")
        Set wsData = wb.Worksheets("Data")
        'Assuming E column has no heading
        'Otherwise, change E1 to the starting cell
        Set nameList = wsData.Range("E1:E" & wsData.Range("E1000000").End(xlUp).Row)
        'Look for names that don't exist and put in an array
        For Each cell In nameList
            On Error Resume Next
            If IsError(WorksheetFunction.Match(cell.Value, wsStaff.Range("A:A"), 0)) Then
                i = i + 1
                ReDim Preserve notFound(1 To i)
                notFound(i) = cell.Value
            End If
        Next cell
        'Extract array to summary sheet
        For i = LBound(notFound()) To UBound(notFound())
            wsSummary.Range("F" & (i + 38)).Value = notFound(i)
        Next i
    End Sub

    Open in new window


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now