0

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.

6
  • @jcbermu: Thanks for the edition. I will take them into account for next time. :)
    – Solarinoos
    Oct 17, 2016 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.
    – Solarinoos
    Oct 17, 2016 at 8:55
  • Do you want the results in column E in comma separated form ?? Oct 17, 2016 at 10:56
  • 1
    Short of sorted reference columns and a helper column, you will need vba. Oct 17, 2016 at 13:27

1 Answer 1

1

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

1
  • 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.
    – Solarinoos
    Oct 18, 2016 at 6:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .