Index-Match Formula

[mikec82]

I've got a spreadsheet that looks like this:

Teacher	Class
John Smith	Algebra
John Smith	Calculus
John Smith	Geometry
Steven Parker	English

<tbody>
</tbody>

I'd like some help if possible, coming up with a formula to put into another sheet, that would put all the classes that individual teaches into one cell. So the outcome would look like:

Teacher	Class
John Smith	Algebra, Calculus, Geometry
Steven Parker	English

<tbody>
</tbody>

Is this possible?

 

[MrKowz]

Here's a VBA solution. Ensure your Teacher list is in column A, and the class list is in Column B. This will create a new sheet with the combined results.

Public Sub TeacherClass()
Dim d       As Object, _
    k       As Variant, _
    rowx    As Long

Dim i       As Long, _
    LR      As Long
    
Dim sWS     As Worksheet, _
    dWS     As Worksheet
    
Set d = CreateObject("scripting.dictionary")

Set sWS = ActiveSheet
Set dWS = Sheets.Add

LR = sWS.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LR
    With sWS
        If Not d.Exists(.Range("A" & i).Value) Then
            'Add to list
            d.Add .Range("A" & i).Value, .Range("B" & i).Value
        Else
            'Append
            d(.Range("A" & i).Value) = d(.Range("A" & i).Value) & ", " & .Range("B" & i).Value
        End If
    End With
Next i

rowx = 2

dWS.Range("A1").Value = "Teacher"
dWS.Range("B1").Value = "Class"

For Each k In d.Keys
    dWS.Range("A" & rowx).Value = k
    dWS.Range("B" & rowx).Value = d(k)
    rowx = rowx + 1
Next k

End Sub

 

[Mike Szczesny]

Can you use something like this? Put your formula in the same place under the Teacher field in your results table. This formula finds unique items. You need to enter Cntrl+Shift+Enter. Copy down. I also put an =iferror(.....," ") around this formula to remove error messages after you get your intended results.

=IFERROR(INDEX($A$2:$A$5,MATCH(0,COUNTIF($A$7:A7,$A$2:$A$5),0))," ") The other formula is a two step process. First you need to create a results range going down a column. Then you can concatenate and transpose these results in one cell. In a separate cell say..D1 put in formula to capture classes for a specific teacher. =IFERROR(INDEX($C$2:$C$5,SMALL(IF($A$2:$A$5=$A$9,ROW($C$2:$C$5)-ROW($C$2)+1),ROWS($D$1:D1)))," ") Use C-S-E to enter Now put your formula to get your subject, subject, results. In this cell enter the formula =concatenate(transpose(D1:D3& " , ")). Highlight the transpose(.....) part of this formula with your mouse. Press F9. Remove the { and }. Hit enter <tbody> </tbody>

<tbody>
</tbody>

<tbody>
</tbody>

Teacher		Class	Algebra
John Smith		Algebra	Calculus
John Smith		Calculus	Geometry
John Smith		Geometry
Steven Parker		English
Teacher		Class
John Smith		Algebra , Calculus , Geometry ,
Steven Parker		English

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>