Concatenating a string in a "Many-to-One" relationship

[pivotpp]

Hi all,

Uploaded a pic of what I would like help in accomplishing: http://imgur.com/9j5n7LZ

Column a have teacher name, column b have student name. Each student can have more than 1 teacher.

For example, adam have all 3 teacher so I list their name Mr Robinson, ms Martinez, mr orwell (separated by a comma). Dan only have 1 teacher so it o Ky list Mr Robinson.

Any thoughts on a formula to accomllish this?

Thanks kindly in advance

 

[Rick Rothstein]

You might be able to do this with formulas if you have the newest version of Excel, but I am using XL2010 and you cannot do what you want with formulas in it. Here is a UDF (user defined function) that creates a function that can be used in a formula.

[table="width: 500"]
[tr]
	[td]Function Teachers(StudentName As String) As String
  Dim R As Long, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "B").End(xlUp))
  For R = 1 To UBound(Data)
    If Data(R, 2) = StudentName Then Teachers = Teachers & ", " & Data(R, 1)
  Next
  Teachers = Mid(Teachers, 3)
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Teachers just like it was a built-in Excel function. For example,

=Teachers(B1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[AhoyNC]

If you have Excel 2010 or Office 365 with the TEXTJOIN function then:
This is an array formula that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C
1	Mr Rob	Adam	Mr Rob, Ms. Mar, Mr Or
2	Mr Rob	Bob	Mr Rob, Ms. Mar, Mr Or
3	Mr Rob	Cindy	Mr Rob, Mr Or
4	Mr Rob	Dan	Mr Rob
5	Mr Rob	Edward	Mr Rob
6	Ms. Mar	Wendy	Ms. Mar
7	Ms. Mar	Adam	Mr Rob, Ms. Mar, Mr Or
8	Ms. Mar	Bob	Mr Rob, Ms. Mar, Mr Or
9	Mr Or	Adam	Mr Rob, Ms. Mar, Mr Or
10	Mr Or	Bob	Mr Rob, Ms. Mar, Mr Or
11	Mr Or	Cindy	Mr Rob, Mr Or
Sheet

 

[Rick Rothstein]

If you have Excel 2010 or Office 365 with the TEXTJOIN function

Typo Alert --- I am pretty sure AhoyNC meant to type 2016, not 2010 as XL2010 does not have the TEXTJOIN function.

 

[AhoyNC]

Thanks Rick, fat fingers on my part. That should have been Excel 2016 as you stated.

 

[jtakw]

Hi,

I believe even with Excel 2016, you Still need an Office 365 subscription to get the TEXTJOIN function...I have 2016 Without 365 and Don't have this function.

If you DO have TEXTJOIN, then AhoyNC has already given you what you need.

If you Don't, and is happy with a UDF, then Rick has already given you that also.

But if you Still want a formula solution, my suggestion requires a Blank or Header row on Top of your data, and a Helper Column (both of which you can "hide" out of sight):

Book1
	A	B	C	D	E
1	teacher	student	teachers	teachers - final results	<-- Helper row
2	mr Robinson	adam	mr Robinson	mr Robinson, ms Martinez, mr Orwell
3	mr Robinson	bob	mr Robinson	mr Robinson, ms Martinez, mr Orwell
4	mr Robinson	cindy	mr Robinson	mr Robinson, mr Orwell
5	mr Robinson	dan	mr Robinson	mr Robinson
6	mr Robinson	edward	mr Robinson	mr Robinson
7	ms Martinez	yasmine	ms Martinez	ms Martinez, mr Orwell
8	ms Martinez	wendy	ms Martinez	ms Martinez
9	ms Martinez	adam	mr Robinson, ms Martinez	mr Robinson, ms Martinez, mr Orwell
10	ms Martinez	bob	mr Robinson, ms Martinez	mr Robinson, ms Martinez, mr Orwell
11	mr Orwell	yasmine	ms Martinez, mr Orwell	ms Martinez, mr Orwell
12	mr Orwell	adam	mr Robinson, ms Martinez, mr Orwell	mr Robinson, ms Martinez, mr Orwell
13	mr Orwell	bob	mr Robinson, ms Martinez, mr Orwell	mr Robinson, ms Martinez, mr Orwell
14	mr Orwell	cindy	mr Robinson, mr Orwell	mr Robinson, mr Orwell
15
16			^^ Helper Column
Sheet390

Cell Formulas
Range	Formula
C2	=IF(COUNTIF(B$2:B2,B2)=1,A2,LOOKUP(2,1/SEARCH(B2,B$1:B1),C$1:C1)&", "&A2)
D2	=LOOKUP(2,1/SEARCH(B2,B$2:B$14),C$2:C$14)

Formulas copied down.

 

[jtakw]

Hi again,

Slightly shorter formula for C2 copied down:

Book1
	A	B	C	D	E
1	teacher	student	teachers	teachers - final results	<-- Helper row
2	mr Robinson	adam	mr Robinson	mr Robinson, ms Martinez, mr Orwell
3	mr Robinson	bob	mr Robinson	mr Robinson, ms Martinez, mr Orwell
4	mr Robinson	cindy	mr Robinson	mr Robinson, mr Orwell
5	mr Robinson	dan	mr Robinson	mr Robinson
6	mr Robinson	edward	mr Robinson	mr Robinson
7	ms Martinez	yasmine	ms Martinez	ms Martinez, mr Orwell
8	ms Martinez	wendy	ms Martinez	ms Martinez
9	ms Martinez	adam	mr Robinson, ms Martinez	mr Robinson, ms Martinez, mr Orwell
10	ms Martinez	bob	mr Robinson, ms Martinez	mr Robinson, ms Martinez, mr Orwell
11	mr Orwell	yasmine	ms Martinez, mr Orwell	ms Martinez, mr Orwell
12	mr Orwell	adam	mr Robinson, ms Martinez, mr Orwell	mr Robinson, ms Martinez, mr Orwell
13	mr Orwell	bob	mr Robinson, ms Martinez, mr Orwell	mr Robinson, ms Martinez, mr Orwell
14	mr Orwell	cindy	mr Robinson, mr Orwell	mr Robinson, mr Orwell
15
16			^^ Helper Column
Sheet390

Cell Formulas
Range	Formula
C2	=IFERROR(LOOKUP(2,1/SEARCH(B2,B$1:B1),C$1:C1)&", ","")&A2
D2	=LOOKUP(2,1/SEARCH(B2,B$2:B$14),C$2:C$14)