00:22 Business Scenario
01:31 Write Code in ChatGPT
04:29 Test and Validate
05:26 Find New Hires
VBA Code used in this video:
_____________________________________________________
Sub CompareEmployeeRecords()
Dim ws2022 As Worksheet, ws2023 As Worksheet, wsResult As Worksheet
Dim lastRow2022 As Long, lastRow2023 As Long, lastRowResult As Long
Dim dict2022 As Object, dict2023 As Object
Dim empID As String, empName As String
Dim i As Long
' Set references to the worksheets
Set ws2022 = ThisWorkbook.Sheets("2022 employee_records")
Set ws2023 = ThisWorkbook.Sheets("2023 employee_records")
' Add a new sheet for the result
Set wsResult = ThisWorkbook.Sheets.Add(After:=ws2023)
wsResult.Name = "Employees_Only_In_2022"
' Find the last row in each sheet
lastRow2022 = ws2022.Cells(ws2022.Rows.Count, "A").End(xlUp).Row
lastRow2023 = ws2023.Cells(ws2023.Rows.Count, "A").End(xlUp).Row
' Create dictionaries to store employee IDs from each year
Set dict2022 = CreateObject("Scripting.Dictionary")
Set dict2023 = CreateObject("Scripting.Dictionary")
' Loop through "2022 employee_records" and store employee IDs in the dictionary
For i = 2 To lastRow2022
empID = ws2022.Cells(i, "A").Value
empName = ws2022.Cells(i, "B").Value
dict2022(empID) = empName ' Using the employee ID as the key
Next i
' Loop through "2023 employee_records" and store employee IDs in the dictionary
For i = 2 To lastRow2023
empID = ws2023.Cells(i, "A").Value
empName = ws2023.Cells(i, "B").Value
dict2023(empID) = empName ' Using the employee ID as the key
Next i
' Loop through the dictionary of 2022 records and check if the employee exists in 2023 records
' If not, add the employee details to the result sheet
lastRowResult = 1 ' Start from row 1 in the result sheet
For Each empID In dict2022.Keys
If Not dict2023.Exists(empID) Then
lastRowResult = lastRowResult + 1
wsResult.Cells(lastRowResult, "A").Value = empID
wsResult.Cells(lastRowResult, "B").Value = dict2022(empID)
End If
Next empID
' Format the result sheet (optional)
With wsResult.Range("A1:B1")
.Font.Bold = True
.Interior.Color = RGB(220, 220, 220)
End With
' Autofit columns to display all content
wsResult.Columns("A:B").AutoFit
' Display a message when the task is completed
MsgBox "Comparison completed. Employees only found in 2022 have been listed in the new sheet.", vbInformation
End Sub
_____________________________________________________
Related Links:
openai.com/blo...
Want to learn how to design a salary structure? Check: bit.ly/3r3KXeW
FREE template for my video: Excel for HR - Create Annual Employee Salary Increase Template from Scratch. You can download and try it out yourself here: bit.ly/2MLLdb7
FREE actual workbook for my video "Split a Master Spreadsheet into Multiple Sheets with 1 click - VBA for Beginner". You can download and try it out yourself here: bit.ly/2UmeX2v
*****More Videos in Playlists*****
Coding with ChatGPT bit.ly/3YtsmWe
Excel Macro - Beginner goo.gl/Yae5nc
Excel Macro/VBA - Splitting a Master File goo.gl/m8CHya
Excel Macro/VBA - Auto-hide Rows or Columns bit.ly/2Mzteb5
Excel vs Power BI: bit.ly/3NQWN51
Power BI for Beginners: bit.ly/3ivKitD
Power BI for Advanced Users: bit.ly/3lE9zmO
Excel for HR goo.gl/JdeVnd
Excel for HR - Master Class goo.gl/LYfq2f
Excel Charts Data Visualization goo.gl/2ao6BP
Excel Vlookup Function goo.gl/kP2Wpz
Excel Pivot Table Function goo.gl/rukkPs
Excel Array Function goo.gl/i4sQH8
Excel Index and Match Function goo.gl/i7VGU4
Excel Solver/Goal Seek Functions goo.gl/FTkTnj
Excel Cell Formatting Solutions goo.gl/gpa6MY
HR Analytics - Merit Matrix goo.gl/Koy7co
HR Analytics - Salary Structure goo.gl/uZBnFa
Excel Tricks goo.gl/TeqGDw
Excel Troubleshooting goo.gl/bdY5by
Fun HR Topics goo.gl/7zVg8h
#ExcelforHR#HRAnalytics#Excel#HR
Негізгі бет Clean Data with ChatGPT: Compare Year over Year data to Find Terminated and New Hire Employees
Пікірлер: 2