I have two columns:

    Column A     Column B

 1  Course ID   Professors  <---- (Column Headers)  
 2  1179/03     Professor-1  
 3  1179/03     
 4  1179/03     
 5  1300/20     Professor-2   
 6  1300/20     Professor-3  
 7  1300/21     Professor-2  
 8  1300/21     Professor-3  
 9  1300/21     Professor-4  
10  1300/21     Professor-4
11  1300/21     Professor-4

Where, to each Course ID can be multiple Professors assigned.

In another part of the same sheet I have a list of Course IDs and I want to insert the assigned professors by means of a formula:

    Column D     Column E

1  Course ID   Professors   <---  (Column Header)  
2  1179/03     Professor-1  
3  1300/20     Professor-2, Professor-3  
4  1300/21     Professor-2, Professor-3, Professor-4

I will manually provide the data in the A, B and D columns, then I want to get the results in the E2, E3, E4 by means of Match/Index or VLOOKUP.
My limit is that, I am not able to add any new column, and possibly, restrict users to sort data according to a certain column.

Can anyone help me, please.

  • @jcbermu: Thanks for the edition. I will take them into account for next time. :) – Dio Oct 17 '16 at 8:37
  • 1
  • @fixer1234: My first priority is to keep the columns as they are. Furthermore, I cannot guarantee that the users sort the reference column. Of course I will do it, if I don't find any other solution. – Dio Oct 17 '16 at 8:55
  • Do you want the results in column E in comma separated form ?? – Gary's Student Oct 17 '16 at 10:56
  • 1
    Short of sorted reference columns and a helper column, you will need vba. – Scott Craner Oct 17 '16 at 13:27

With data similar to your posted data this short macro:

Sub Roster()
    Dim rc As Long, i As Long, j As Long, v As String
    Dim nA As Long, nB As Long, nD As Long, vv As String

    rc = Rows.Count
    nA = Cells(rc, 1).End(xlUp).Row
    nB = Cells(rc, 2).End(xlUp).Row
    nD = Cells(rc, 4).End(xlUp).Row

    For i = 2 To nD
        v = Cells(i, 4)
        vv = ""
        For j = 2 To nA
            If v = Cells(j, 1) And Cells(j, 2) <> "" And InStr(1, vv, Cells(j, 2)) = 0 Then
                vv = vv & "," & Cells(j, 2)
            End If
        Next j
        Cells(i, 5) = Mid(vv, 2)
    Next i
End Sub

will produce:

enter image description here

  • Thank you so much Gary for the macro. It is really helpful. Would you help me please to customize it a bit. Actually, the table I inserted here is too simplified. In my original file, the professors' full name are separated into two columns of Last name and First name. Since some professors have the same family name, is it possible to achieve a result like this: Newton, Issac; Einstein, Albert. – Dio Oct 18 '16 at 6:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.