One-to-many concatenation problem| Please help!

James12513

New Member
Joined
Jul 24, 2014
Messages
27
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>
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Add the following function code for ACONCAT to your workbook using Alt+F11...
Rich (BB code):
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:
Rich (BB code):
=REPLACE(ACONCAT(IF(Sheet1!$B$2:$B$20=$A2,", "&Sheet1!$A$2:$A$20,"")),1,2,"")
 
Last edited by a moderator:

James12513

New Member
Joined
Jul 24, 2014
Messages
27
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
@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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,646
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top