Clean Data with ChatGPT: Extract Unique Values from Multiple Column Datasets
00:17 Business Scenario
01:26 Write Code in ChatGPT
02:35 Second Trial
VBA Code used in this video:
_____________________________________________________
Sub CreateUniqueLists()
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim uniqueValuesC As Collection
Dim uniqueValuesD As Collection
Dim uniqueValuesE As Collection
Dim cell As Range
Dim newValue As Variant
' Set the source worksheet
On Error Resume Next
Set wsSource = ThisWorkbook.Worksheets("2023 employee_records")
On Error GoTo 0
If wsSource Is Nothing Then
MsgBox "Worksheet '2023 employee_records' not found!", vbExclamation
Exit Sub
End If
' Create new target worksheet
Set wsTarget = ThisWorkbook.Worksheets.Add
wsTarget.Name = "Unique_Values_List"
' Find unique values in column C and write to column A in the target sheet
Set uniqueValuesC = New Collection
On Error Resume Next
For Each cell In wsSource.Range("C2:C" & wsSource.Cells(wsSource.Rows.Count, "C").End(xlUp).Row)
uniqueValuesC.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0
wsTarget.Range("A1").Value = "Unique Values in Column C"
For Each newValue In uniqueValuesC
wsTarget.Cells(wsTarget.Rows.Count, "A").End(xlUp).Offset(1, 0).Value = newValue
Next newValue
' Find unique values in column D and write to column B in the target sheet
Set uniqueValuesD = New Collection
On Error Resume Next
For Each cell In wsSource.Range("D2:D" & wsSource.Cells(wsSource.Rows.Count, "D").End(xlUp).Row)
uniqueValuesD.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0
wsTarget.Range("B1").Value = "Unique Values in Column D"
For Each newValue In uniqueValuesD
wsTarget.Cells(wsTarget.Rows.Count, "B").End(xlUp).Offset(1, 0).Value = newValue
Next newValue
' Find unique values in column E and write to column C in the target sheet
Set uniqueValuesE = New Collection
On Error Resume Next
For Each cell In wsSource.Range("E2:E" & wsSource.Cells(wsSource.Rows.Count, "E").End(xlUp).Row)
uniqueValuesE.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0
wsTarget.Range("C1").Value = "Unique Values in Column E"
For Each newValue In uniqueValuesE
wsTarget.Cells(wsTarget.Rows.Count, "C").End(xlUp).Offset(1, 0).Value = newValue
Next newValue
' Autofit columns in the target sheet
wsTarget.Columns.AutoFit
MsgBox "Unique value lists created successfully!", vbInformation
End Sub
______________________________________
Related Links:
https://openai.com/blog/chatgpt
Want to learn how to design a salary structure? Check: https://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: https://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: https://bit.ly/2UmeX2v
*****More Videos in Playlists*****
Coding with ChatGPT https://bit.ly/3YtsmWe
Excel Macro - Beginner https://goo.gl/Yae5nc
Excel Macro/VBA - Splitting a Master File https://goo.gl/m8CHya
Excel Macro/VBA - Auto-hide Rows or Columns http://bit.ly/2Mzteb5
Excel vs Power BI: https://bit.ly/3NQWN51
Power BI for Beginners: https://bit.ly/3ivKitD
Power BI for Advanced Users: http://bit.ly/3lE9zmO
Excel for HR https://goo.gl/JdeVnd
Excel for HR - Master Class https://goo.gl/LYfq2f
Excel Charts Data Visualization https://goo.gl/2ao6BP
Excel Vlookup Function https://goo.gl/kP2Wpz
Excel Pivot Table Function https://goo.gl/rukkPs
Excel Array Function https://goo.gl/i4sQH8
Excel Index and Match Function https://goo.gl/i7VGU4
Excel Solver/Goal Seek Functions https://goo.gl/FTkTnj
Excel Cell Formatting Solutions https://goo.gl/gpa6MY
HR Analytics - Merit Matrix https://goo.gl/Koy7co
HR Analytics - Salary Structure https://goo.gl/uZBnFa
Excel Tricks https://goo.gl/TeqGDw
Excel Troubleshooting https://goo.gl/bdY5by
Fun HR Topics https://goo.gl/7zVg8h
#ExcelforHR#HRAnalytics#Excel#HR