Require formula

[Ron99]

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID

[/TD]
[TD]CODE
[/TD]
[TD]OUTPUT
[/TD]
[/TR]
[TR]
[TD]001
[/TD]
[TD]AM
[/TD]
[TD]AM,AS,PA
[/TD]
[/TR]
[TR]
[TD]001
[/TD]
[TD]AS
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]001
[/TD]
[TD]PA
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]002
[/TD]
[TD]ZE
[/TD]
[TD]ZE
[/TD]
[/TR]
[TR]
[TD]003
[/TD]
[TD]RO
[/TD]
[TD]RO,IH
[/TD]
[/TR]
[TR]
[TD]003
[/TD]
[TD]IH
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.