One-to-many concatenation problem| Please help!

[James12513]

Hello all,

My friends at work and I have been bashing our heads together to try to come up with the most simple and efficient method of the below problem..

After a day of drawing up blanks we thought our last option was to come here for help - so we really would appreciate any ideas!!

What we want our final table to look this from the data below:
(please note this is a sample - there will be thousands of students to hundreds of teachers)

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Teacher
[/TD]
[TD="align: center"]Student
[/TD]
[/TR]
[TR]
[TD]David Smith
[/TD]
[TD]Billy, Mike, Sarah, James, Maggie, Sophie
[/TD]
[/TR]
[TR]
[TD]Rachel Carne
[/TD]
[TD]Karen, Sara, Hassan, Amit, Andrew
[/TD]
[/TR]
[TR]
[TD]Sophie Patel
[/TD]
[TD]David, Paul, Peter, Rish, Rachel
[/TD]
[/TR]
[TR]
[TD]Zack Brown
[/TD]
[TD]Ameeta, Melissa, Abdul, Andy
[/TD]
[/TR]
</tbody>[/TABLE]



DATA:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Student
[/TD]
[TD="align: center"]Teacher
[/TD]
[/TR]
[TR]
[TD]Billy
[/TD]
[TD]David Smith
[/TD]
[/TR]
[TR]
[TD]Mike
[/TD]
[TD]David Smith
[/TD]
[/TR]
[TR]
[TD]Sarah
[/TD]
[TD]David Smith
[/TD]
[/TR]
[TR]
[TD]Karen
[/TD]
[TD]Rachel Carne
[/TD]
[/TR]
[TR]
[TD]David
[/TD]
[TD]Sophie Patel
[/TD]
[/TR]
[TR]
[TD]James
[/TD]
[TD]David Smith
[/TD]
[/TR]
[TR]
[TD]Ameeta
[/TD]
[TD]Zack Brown
[/TD]
[/TR]
[TR]
[TD]Paul
[/TD]
[TD]Sophie Patel
[/TD]
[/TR]
[TR]
[TD]Sara
[/TD]
[TD]Rachel Carne
[/TD]
[/TR]
[TR]
[TD]Melissa
[/TD]
[TD]Zack Brown
[/TD]
[/TR]
[TR]
[TD]Abdul
[/TD]
[TD]Zack Brown
[/TD]
[/TR]
[TR]
[TD]Hassan
[/TD]
[TD]Rachel Carne
[/TD]
[/TR]
[TR]
[TD]Maggie
[/TD]
[TD]David Smith
[/TD]
[/TR]
[TR]
[TD]Sophie
[/TD]
[TD]David Smith
[/TD]
[/TR]
[TR]
[TD]Peter
[/TD]
[TD]Sophie Patel
[/TD]
[/TR]
[TR]
[TD]Andy
[/TD]
[TD]Zack Brown
[/TD]
[/TR]
[TR]
[TD]Amit
[/TD]
[TD]Rachel Carne
[/TD]
[/TR]
[TR]
[TD]Andrew
[/TD]
[TD]Rachel Carne
[/TD]
[/TR]
[TR]
[TD]Rish
[/TD]
[TD]Sophie Patel
[/TD]
[/TR]
[TR]
[TD]Rachel
[/TD]
[TD]Sophie Patel
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

See the code I posted in my mini-blog article here...

<!-- title / author block -->Redistribute a Delimited Column Of Data into Separate Rows (Keeping Other Data As Is)

 

[Aladin Akyurek]

Add the following function code for ACONCAT to your workbook using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Let Sheet1, A1:B21, house the data sample.

Sheet2

A2 houses a teacher like David Smith.

B2, control+shift+enter, not just enter:

=REPLACE(ACONCAT(IF(Sheet1!$B$2:$B$20=$A2,", "&Sheet1!$A$2:$A$20,"")),1,2,"")

 

[James12513]

Hi Aladin,

I can't seem to get this to work, sorry I'm a newbie...

1. I press ALT + F11

2. Microsoft Excel Projects - "ThisWorkbook" - at top of drop down list of editor select "Workbook"

3.
Private Sub Workbook_Open()

Paste your code here.

End Function

4. Hit run - "Compile error: Expected end sub".


Do you mind telling me step by step what to do please?

Kind regards,

James


@Rick - I can't seem to get your macro to work either. Do I setup a new macro and paste the code inside?

 

[RoryA]

You need to add the function code to a new module (Insert - Module in the VB Editor), not the ThisWorkbook module.

 

[James12513]

Thanks Rory! Ah it's brilliant Aladin!!

@Rick - will try to get yours to work too

 

[Rick Rothstein]

@Rick - I can't seem to get your macro to work either. Do I setup a new macro and paste the code inside?

I am not sure what you mean by "setup a new macro and past the code inside"... the code I posted in my blog is complete unto itself... all you have to do is copy/paste it into any general Module and make sure the four Const statements are assigned values that match your particular setup. If you still have trouble, send me your workbook and let me see if I can figure out what the problem is. My email address is...

rick DOT news AT verizon DOT net