List names not on a list via VBA
Hello,
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 ;)
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 ;)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In let's say sheet Staff column B row 3 (or anywhere else ...) type:
=IF(MATCH(A3;Data!E:E;0);"
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:
sheets("Summary").range("F
Selection.Sort Key1:=Worksheets("Summary"
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.