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)

Teacher	Student
David Smith	Billy, Mike, Sarah, James, Maggie, Sophie
Rachel Carne	Karen, Sara, Hassan, Amit, Andrew
Sophie Patel	David, Paul, Peter, Rish, Rachel
Zack Brown	Ameeta, Melissa, Abdul, Andy

<tbody>
</tbody>

DATA:

Student	Teacher
Billy	David Smith
Mike	David Smith
Sarah	David Smith
Karen	Rachel Carne
David	Sophie Patel
James	David Smith
Ameeta	Zack Brown
Paul	Sophie Patel
Sara	Rachel Carne
Melissa	Zack Brown
Abdul	Zack Brown
Hassan	Rachel Carne
Maggie	David Smith
Sophie	David Smith
Peter	Sophie Patel
Andy	Zack Brown
Amit	Rachel Carne
Andrew	Rachel Carne
Rish	Sophie Patel
Rachel	Sophie Patel

<tbody>
</tbody>

 

[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