[FORMULA] Return String Value of Multiple Cells In One Cell

[xTwisteDx]

I have a batch of data in the following format.

This is my input:

[TABLE="width: 500"]
<tbody>[TR]
[TD]GROUP[/TD]
[TD]EMPLOYEE[/TD]
[/TR]
[TR]
[TD]someEmail1@here.com[/TD]
[TD]Jane Doe[/TD]
[/TR]
[TR]
[TD]someOtherEmail@there.com[/TD]
[TD]Johnny Lee[/TD]
[/TR]
[TR]
[TD]someOtherEmail@there.com[/TD]
[TD]Jane Doe[/TD]
[/TR]
[TR]
[TD]someEmail1@here.com[/TD]
[TD]Jacob Smith[/TD]
[/TR]
</tbody>[/TABLE]

My Expected Output would be:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Jane Doe[/TD]
[TD]someEmail1@here.com someOtherEmail@there.com[/TD]
[/TR]
[TR]
[TD]Johnny Lee[/TD]
[TD]someOtherEmail@there.com[/TD]
[/TR]
[TR]
[TD]Jacob Smith[/TD]
[TD]someEmail1@here.com[/TD]
[/TR]
</tbody>[/TABLE]

Essentially I need a formula that will combine the values of the groups column but match it
on the names in the rows. Please note that the names are random, the emails are random, and there are repeating names.

 

[Eric W]

Perhaps:

	A	B	C	D	E	F
EMPLOYEE	Email
Jane Doe	someEmail1@here.com	someOtherEmail@there.com
Johnny Lee	someOtherEmail@there.com
Jacob Smith	someEmail1@here.com

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]GROUP[/TD]
[TD="bgcolor: #FAFAFA"]EMPLOYEE[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]someEmail1@here.com[/TD]
[TD="bgcolor: #FAFAFA"]Jane Doe[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]someOtherEmail@there.com[/TD]
[TD="bgcolor: #FAFAFA"]Johnny Lee[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]someOtherEmail@there.com[/TD]
[TD="bgcolor: #FAFAFA"]Jane Doe[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]someEmail1@here.com[/TD]
[TD="bgcolor: #FAFAFA"]Jacob Smith[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet11​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IFERROR(INDEX($B$2:$B$10,AGGREGATE(15,6,(ROW($B$2:$B$10)-ROW($B$2)+1)/(($B$2:$B$10<>"")*(COUNTIF($D$1:$D1,$B$2:$B$10)=0)),1)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IFERROR(INDEX($A$2:$A$10,AGGREGATE(15,6,(ROW($B$2:$B$10)-ROW($B$2)+1)/(($B$2:$B$10=$D2)*(COUNTIF($D2:D2,$A$2:$A$10)=0)),1)),"")&""[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Put the D2 formula in and drag down. Put the E2 formula in and drag down and across as needed.

 

[xTwisteDx]

Whenever I try that formula it only returns 3 names for the D2 column.

 

[xTwisteDx]

Whenever I try that formula it only returns 3 names for the D2 column.

Disregard, I forgot to update the cell references. I do however have one thing I need to figure out with your E2 formula. Instead of dragging it out, I need it to all be in the same cell and spaced by a new line within that cell. Thoughts?

 

[kweaver]

If you have Excel 365, you can use TEXTJOIN in the E column (from Eric's table) and flll down to only the unique values showing in column D.
It's an array formula, entered with CTRL+Shift+Enter

=TEXTJOIN(" ",TRUE,IF(D2=A2:A10,B2:B10,""))

You can make the space delimiter CHAR(10) and then wrap text if you want them on separate lines.