Require formula

[Ron99]

ID	CODE	OUTPUT
001	AM	AM,AS,PA
001	AS
001	PA
002	ZE	ZE
003	RO	RO,IH
003	IH

<tbody>
</tbody>

I require formula for the above data, I have typed the output that I require, I require formula the output column

Thanks,
Ron

 

[Aladin Akyurek]

Assuming that TEXTJOIN is available on your Excel system...

In C2 control+shift+enter, not just enter, and copy down:

=IF(ISNA(MATCH(A2,$A$1:A1,0)),TEXTJOIN(",",TRUE,IF($A$2:$A$7=A2,$B$2:$B$7,"")),"")

 

[Ron99]

Hi,

I am using excel 2016, TEXTJOIN function does not show up.

 

[Aladin Akyurek]

Hi,

I am using excel 2016, TEXTJOIN function does not show up.

In that case:

If the native function TEXTJOIN is not available on your system...

In C2 control+shift+enter, not just enter, and copy down:

=IF(ISNA(MATCH(A2,$A$1:A1,0)),REPLACE(aconcat(IF($A$2:$A$7=$A2,", "&$B$2:$B$7,"")),1,1,""),"")​

For this formula to work, add the following code for ACONCAT as a Module 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​

 

[Ron99]

Thank you very much for your time. That helps.

 

[Aladin Akyurek]

Thank you very much for your time. That helps.

You are welcome.