How to compare two spreadsheets for identical employee numbers?

caparcona

New Member
Joined
Mar 9, 2022
Messages
19
Office Version
  1. 2021
Platform
  1. Windows
I just promoted into this position and am learning Excel for the first time. I have a "not working" sheet and a "didn't work yesterday" sheet in the same workbook. I'm trying to compare people who skipped work yesterday against who skipped work today and "Ctrl+C, CRTL+V" was getting old very fast so I decided to try to teach myself VBA in order to get more work done. Thus far I have:
Sub comparison()
Dim B1 As Range
Dim Dic As Object
Set Dic = CreateObject("Scripting.Dictionary")
Dim ws1 As Worksheet
Set ws1 = Application.Worksheets("Current List")
Dim ws2 As Worksheet
Set ws2 = Application.Worksheets("Prev NCNS Here")


With Sheets(ws1)
For Each B1 In ws2.Range("B:B",
End With

With Sheets(ws2)
End With

End Sub
I've gotten a little stuck since I am not sure why I Dimmed B1 As Range last week and I'm not sure how to do anything further. I need to take Column B "Current List" (Sheet 1) and compare it against Column B "Prev NCNS Here" (Sheet 2), and eventually I want to paste whatever is in Column C Prev NCNS Here into Column C Current List (I have already done this before, I don't need help with Column C).

What I need help with is how to compare the B columns. Copying the Column C info for that row will be the confirmation that the person is on both lists.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
I havent been able to test this but maybe something like this will work for you
VBA Code:
Sub Comparison()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim Rng1 As Range, Rng2 As Range, Ans As Range, cel As Range
Dim LkFor As String

Set ws1 = Sheets("Current List")
Set ws2 = Sheets("Prev NCNS Here")
Set Rng1 = ws1.Range("B" & ws1.Cells(Rows.count, 2).End(xlUp).row)
Set Rng2 = ws2.Range("B" & ws2.Cells(Rows.count, 2).End(xlUp).row)

For Each cel In Rng1
LkFor = cel
                       
Set Ans = Rng2.Find(what:=LkFor, LookIn:=xlValues, _
                lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
               
    If Not Ans Is Nothing Then
        ws2.Cells(Ans.row, 3).Copy ws1.Cells(cel.row, 3)
    Else
        MsgBox "Employee Number " & cel & " not found"
    End If

Next cel

End Sub
 
Upvote 0
Here is my answer to the other thread that was closed. Same code just update the sheet names and change column D to C.

VBA Code:
Sub CompareNames()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim sht1Lastrow As Long
    Dim sht2Lastrow As Long
    Dim i As Long
    Dim j As Long
    Dim note As Long
 
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")

    Sheets(ws1).Select  'Select ws1 and count rows
    With ActiveSheet
        sht1Lastrow = .UsedRange.Rows.Count + .UsedRange.Row - 1
    End With

    Sheets(ws2).Select  'Select ws2 and count rows
    With ActiveSheet
        sht2Lastrow = .UsedRange.Rows.Count + .UsedRange.Row - 1
    End With
 
    note = 1  'Begin with row 2 due to Headers
 
    For i = 1 To sht1Lastrow
        For j = 1 To sht2Lastrow
            If ws2.Cells(j, 1) = ws1.Cells(i, 1) Then   'Check for duplicate names
                note = note + 1
                ws2.Cells(note, 4).Value = ws2.Cells(j, 1).Value
                ws1.Cells(note, 4).Value = ws2.Cells(j, 1).Value
            End If
        Next j
    Next i
 
End Sub
 

Attachments

  • forCompareNames.jpg
    forCompareNames.jpg
    51.3 KB · Views: 8
Last edited by a moderator:
Upvote 0
@DacEasy
When posting vba code, please use the various available code tags. My signature block below has more details.
I have added the tags this time in the post above.
 
Upvote 0
Thanks for the help, guys! Sorry I haven't responded, work has been swamped. You've been a big help, though, and I've learned from your answers to make my own code. This site is the best!
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top