Compare two Listbox for matching values

Question

I'm trying to achieve a simple way to assign employee for a specific job.

Having Table1 (Employee skills), and have Table2 (Job Requirements). I want to be able to check if an employee has enough skills to be capable of doing a certain job.

This way, I have two listbox, Listbox1 as Table1 and Listbox2 as Table2. I have the following code to find the index of a specific line in Listbox1 and Listbox2

Dim i As Integer
Dim j As Integer
With ListBox1
.MultiSelect = fmMultiSelectSingle
.ListIndex = -1
.MultiSelect = fmMultiSelectMulti
For i = 0 To .ListCount - 1
 For j = 0 To .columnCount - 1
 If ComboBox2.Value = .Column(j, i) Then
 .ListIndex = i
 .Selected(i) = True
 End If
 Next j
Next i
End With

After identifying the row of the employee and the row of the job in the Listbox1 and 2 I want to check match values between them, and show the values that match on Listbox3. I currently have the following code, but only works with Listbox with one column:

Dim i As Long, j As Long
Dim fMatch As Long

For i = 1 To ListBox1.ListCount
  fMatch = False
  For j = 1 To ListBox2.ListCount
    If ListBox1.List(i - 1) = ListBox2.List(j - 1) Then
      fMatch = True
      Exit For
    End If
  Next j
  If fMatch Then
    ListBox3.AddItem ListBox1.List(i - 1)
  End If
Next i

I'm also trying to get a list of best employees to fit the role in listbox3.

artnib · Accepted Answer · 2021-02-22 13:46:39Z

1

'Identify and select employee and job rows in ListBox1 and ListBox2
...
'Display skill matches from Course1..3 columns in ListBox3
ShowMatches ListBox1.ListIndex, ListBox2.ListIndex
...    
Private Sub ShowMatches(EmployeeRow As Long, JobRow As Long)
      Dim i As Long, j As Long, course
      If EmployeeRow = -1 Then Exit Sub
      If JobRow = -1 Then Exit Sub
      ListBox3.Clear
      'skip column 0 as it doesn't contain skill information
      For i = 1 To UBound(ListBox1.List, 2)
        For j = 1 To UBound(ListBox2.List, 2)
          course = ListBox1.List(EmployeeRow, i)
          If course = ListBox2.List(JobRow, j) Then
            ListBox3.AddItem course
          End If
        Next j
      Next i
    End Sub

edited Feb 22, 2021 at 13:46

answered Feb 21, 2021 at 15:46

artnib

5084 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ammadeux Over a year ago

Hello, thank you very much for the reply. How do you declare the variable "course"? is it as Long aswell? Also am I suposed to add this code as it is and on a commandbutton write the following: "call ShowMatches"?

artnib Over a year ago

course variable has default Variant type as it can store dates and strings while iterating listbox columns. You are supposed to write something like Call ShowMatches(EmployeeRow, JobRow) or just ShowMatches EmployeeRow, JobRow.

Ammadeux Over a year ago

I'm getting "Could not get list property. Invalid argument" when I run it. I'm putting the (.xlsm) file here, I might be missing something. drive.google.com/file/d/1qWf1DNQwkimQLBsr13ato6SMSQtEjYCx/…

artnib Over a year ago

I've fixed error with detecting column count (see updated answer).

Ammadeux Over a year ago

Thank you very much, runs like a glove. Only have to make sure the course names are exactly the same. I'm going to accept your answers! Is it possible to get the code of second part of the question? Having a commandbutton to choose a job and have listbox3 listing the most qualified people for it, in order. Cheers!

Collectives™ on Stack Overflow

Compare two Listbox for matching values

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related