Excel - to match employees from multiple lists

Hi All - I need your assistance with Excel. I have a worksheet with the following columns.

EmployeeNumber (Column A), Employee_Name (Column B), EmployeeName (Column C),username (Column D) ,email (Column E) and Employee_Number (Column F)

Column A and column B contains the employee numbers and names for a single department, however, the rest of the columns C, D, E, and F contain employee information for the entire company (but column F is sometimes blank.)

The number of employees for a single department are about 700  and the total number of the entire company is about 50000,

I would like to check if the employee from employee_name (column B) exist in the EmployeeName (Column C), if it does then get the ,username (Column D) ,email (Column E) and Employee_Number (Column F)  for the employee to be copied to another sheet. if employee_name (column B) does not exist in EmployeeName (Column C) then ignore.

Is it possible to automated this rather than manual? It will take me a log time if I do this manually.

I have attached a sample spreadsheet, there are two worksheets, worksheet 'Before' contains all columns and worksheet 'After' contains the results.

I hope my explanation make sense, if not please by all means ask me question.

Many Thanks
Jose
Example.xlsx
jose11auAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Please try this....
In the attached, click the button Extract Data on After sheet to get the desired output.
If you have any issue downloading and opening the workbook due to a temporary bug in the forum right now, please download and save the workbook on your system and then open it.
Otherwise you can just paste the code given below into a New Module of your sample workbook you provided.
Sub MatchAndExtractDetails()
Dim sws As Worksheet, dws As Worksheet
Dim x, y, z()
Dim i As Long, lr As Long, j As Long
Dim dict As Object
Set sws = Sheets("Before")     'Source sheet with Data
Set dws = Sheets("After")        'Output Sheet
Set dict = CreateObject("Scripting.Dictionary")
lr = sws.Cells(Rows.Count, 3).End(xlUp).Row
y = sws.Range("C1:C" & lr).Value
For i = 1 To UBound(y, 1)
    dict.Item(y(i, 1)) = y(i, 1)
Next i
dws.Cells.Clear
x = sws.Range("A1").CurrentRegion.Value
ReDim Preserve z(1 To UBound(x, 1), 1 To 5)
For i = 1 To UBound(x, 1)
    If dict.exists(x(i, 2)) Then
        j = j + 1
        z(j, 1) = x(i, 1)
        z(j, 2) = x(i, 2)
        z(j, 3) = x(i, 4)
        z(j, 4) = x(i, 5)
        z(j, 5) = x(i, 6)
    End If
Next i
dws.Range("A1").Resize(UBound(z, 1), 5).Value = z
MsgBox "Data has been extracted successfully.", vbInformation, "Done!"
End Sub

Open in new window

Example.xlsm
0
 
Rgonzo1971Commented:
Hi,

You could use a helper's column to filter the data

=ISNUMBER(MATCH(C2,B:B,0))

Open in new window

But beware with such a large amount of names you could have duplicates or misspelled ones

Regards
Example_V1.xlsx
0
 
jose11auAuthor Commented:
Thank you so much for your help guys.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad I could help.
0
All Courses

From novice to tech pro — start learning today.