#### James12513

##### New Member
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>

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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:

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()

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?

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

Thanks Rory! Ah it's brilliant Aladin!!

@Rick - will try to get yours to work too

@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

Replies
6
Views
1K
Replies
5
Views
6K
Replies
5
Views
5K

1,219,006
Messages
6,145,708
Members
450,635
Latest member
Rookie3510

### 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.

### Which adblocker are you using?

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

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