Concatenate column B based on info in column A

[Thaleus]

Hoping someone can help with my issue below. I am trying to concatenate any codes in the "property codes" column based on the "part number" column. Sample data below. Basically I want a property code string that starts when there is any non-blank in column A and ends when the next non-blank in column A occurs. Is this possible? Thank you in advanced!


[TABLE="width: 338"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Part Number[/TD]
[TD]Property Codes[/TD]
[TD] [/TD]
[TD]Goal[/TD]
[/TR]
[TR]
[TD]98793[/TD]
[TD]AA5[/TD]
[TD] [/TD]
[TD]AA5BR4[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]BR4[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]93847[/TD]
[TD]AA5[/TD]
[TD] [/TD]
[TD]AA5BR3DD4[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]BR3[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]DD4[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]93425[/TD]
[TD]AA5[/TD]
[TD] [/TD]
[TD]AA5BR3DD2[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]BR3[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]DD2[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Part Number[/td][td]Property Codes[/td][td]Goal[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

98793​

[/td][td]AA5[/td][td]AA5BR4[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td]BR4[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

93847​

[/td][td]AA5[/td][td]AA5BR3DD4[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td]BR3[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td]DD4[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

93425​

[/td][td]AA5[/td][td]AA5BR3DD2[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][td]BR3[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td][/td][td]DD2[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

93465​

[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet20[/td][/tr][/table]

Array formula in cell C2:
=IF(A2<>"",TEXTJOIN("",TRUE,B2:INDEX(B2:$B$1000,MATCH(FALSE,ISBLANK(A3:$A$1000),0))),"")

How to enter an array formula
1. Copy formula
2. Double click on cell C2
3. Paste formula
4. Press and hold CTRL + SHIFT
5. Press Enter

 

[Thaleus]

Thank you very much for the quick response! I tried your recommendation and I get the below "#NAME?" with the error "the formula contains unrecognized text" tried without Ctrl+shft+Enter and with it (which adds { }) with same result. I'm on Excel 2016 if that matters? I'm doing something else wrong?

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Part[/TD]
[TD="width: 64"]Codes[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]AB[/TD]
[TD="align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]BB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]GF[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DF[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Oscar Cronquist]

I am also on Excel 2016, I am not sure why you get the #NAME error. I copied the formula back to my worksheet and it works here.

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Part[/td][td]Codes[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]A[/td][td]AB[/td][td]ABAC[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td]AC[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]C[/td][td]BB[/td][td]BBBF[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td]BF[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]G[/td][td]GF[/td][td]GFDF[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td]DF[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]F[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet21[/td][/tr][/table]

 

[Fluff]

I believe that TEXTJOIN is only available with Office 365.

 

[Thaleus]

Yes, TEXTJOIN appears to be my issue, and since I am using a work computer, I am not allowed (IT decision) to make any updates/upgrades to my PC....