We help IT Professionals succeed at work.

List names not on a list via VBA

gisvpn asked
Medium Priority
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 ;)
Watch Question

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.
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


Explore More ContentExplore courses, solutions, and other research materials related to this topic.