Index-Match Formula

[mikec82]

I've got a spreadsheet that looks like this:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Teacher[/TD]
[TD]Class[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Algebra[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Calculus[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Geometry[/TD]
[/TR]
[TR]
[TD]Steven Parker[/TD]
[TD]English[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Teacher[/TD]
[TD]Class[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Algebra, Calculus, Geometry[/TD]
[/TR]
[TR]
[TD]Steven Parker[/TD]
[TD]English[/TD]
[/TR]
</tbody>[/TABLE]


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. [TABLE="width: 94"]
<tbody>[TR]
[TD="width: 94"] =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. [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =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[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 424"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 424"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Teacher[/TD]
[TD][/TD]
[TD]Class[/TD]
[TD]Algebra[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD][/TD]
[TD]Algebra[/TD]
[TD]Calculus[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD][/TD]
[TD]Calculus[/TD]
[TD]Geometry[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD][/TD]
[TD]Geometry[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Steven Parker[/TD]
[TD][/TD]
[TD]English[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Teacher[/TD]
[TD][/TD]
[TD]Class[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD][/TD]
[TD]Algebra , Calculus , Geometry , [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Steven Parker[/TD]
[TD][/TD]
[TD]English[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]