Concatenate when blank cells are present

[nburaq]

Hi Gents,
I am very new to this forum and I feel myself very lucky to find this forum because this forum have lots of resources and professionals to help people! Well, I have a simple and very annoying concatenate problem. I tried lots of formulas and none of them didnt work properly to find my problem. I think only solution seem VBA. I would like to read the value in column B and write this value in column D. However, column B fixed 2 empty cells until next data repeats. I have attached the image shows how final data should look like and mini sheet to play with it. Thanks for your help and this powerful community.

 

[Joe4]

Welcome to the Board!

Here is a VBA solution:

Sub CombineCodes()

    Dim lr As Long
    Dim r As Long
    Dim n As Long
    Dim str As String
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column B
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Loop through all rows starting in row 2
    For r = 2 To lr
'       Check to see if in row 2, or current row is populated and above row is blank
        If (r = 2) Or (Cells(r, "B") <> "" And Cells(r - 1, "B") = "") Then
            str = ""
            n = r
            Do
'               Build string
                str = str & Cells(n, "B") & "/"
'               Increment secondary row counter
                n = n + 1
'               Exit when hit blank row or last row
                If Cells(n, "B") = "" Or n > Rows.Count Then Exit Do
            Loop
'           Populate column D (removing slash on end)
            Cells(r, "D") = Left(str, Len(str) - 1)
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[Joe4]

If the number of consecutive codes were never more than a certain number in a row, you could use a brute-force formulaic approach instead like posted below (place in cell D2 and copy down):

=IF(AND(D2<>"",OR(D1="",ROW()=2)),B2 & IF(B3<>"","/" & B3,"") & IF(B4<>"","/" & B4,""),"")

This works if there are never more than three consecutive listings.
If there are more, but it is definitely limited/cut-off at a certain number, you could repeat the sections of the formula I have shown in color (red is for the 2nd, blue is for the 3rd).
Hopefully, you see the pattern/logic.

Someone may have a better formulaic approach (there are some formulas wizards on this site), but this is the best that I can do.

 

[Fluff]

A formula solution

+Fluff 1.xlsm
	A	B	C	D
1
2	a	1Y		1Y
3
4
5	b	1Y		1Y
6
7
8	c	1Y		1Y/2Y
9		2Y
10
11
12	d	4M		4M/1Y/5Y
13		1Y
14		5Y
15
Master

Cell Formulas
Range	Formula
D2:D12	D2	=IF(A2<>"",TEXTJOIN("/",,B2:INDEX(B2:B50,IFNA(MATCH("*",A3:A50,0),40))),"")
D13:D15	D13	=IF(A13<>"",TEXTJOIN("/",,B13:INDEX(B13:B61,IFNA(MATCH("*",A14:A61,0),50))),"")

 

[nburaq]

I could not believe my eyes when I saw the solutions, you were so fast to answer my question! Thanks for your help and I am glad that I became a member of this forum I knew that I was at right place!

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[jtakw]

Hi,

Another formula option for the Pre-365 readers:

Book3.xlsx
	A	B	C	D
1
2	a	1Y		1Y
3
4
5	b	1Y		1Y
6
7
8	c	1Y		1Y/2Y
9		2Y
10
11
12	d	4M		4M/1Y/5Y
13		1Y
14		5Y
Sheet890

Cell Formulas
Range	Formula
D2:D14	D2	=IF(A2<>"",SUBSTITUTE(TRIM(B2&" "&B3&" "&B4)," ","/"),"")