Concatenate when blank cells are present

nburaq

Board Regular
Joined
Apr 2, 2021
Messages
220
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 

Attachments

  • help.png
    help.png
    15.8 KB · Views: 33

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Welcome to the Board!

Here is a VBA solution:
VBA Code:
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
 
Upvote 0
Solution
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):
Rich (BB code):
=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.
 
Upvote 0
A formula solution
+Fluff 1.xlsm
ABCD
1
2a1Y1Y
3 
4 
5b1Y1Y
6 
7 
8c1Y1Y/2Y
92Y 
10 
11 
12d4M4M/1Y/5Y
131Y 
145Y 
15 
Master
Cell Formulas
RangeFormula
D2:D12D2=IF(A2<>"",TEXTJOIN("/",,B2:INDEX(B2:B50,IFNA(MATCH("*",A3:A50,0),40))),"")
D13:D15D13=IF(A13<>"",TEXTJOIN("/",,B13:INDEX(B13:B61,IFNA(MATCH("*",A14:A61,0),50))),"")
 
Upvote 0
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! (y)
 
Upvote 0
You are welcome.
Glad we were able to help!
:)
 
Upvote 0
Hi,

Another formula option for the Pre-365 readers:

Book3.xlsx
ABCD
1
2a1Y1Y
3 
4 
5b1Y1Y
6 
7 
8c1Y1Y/2Y
92Y 
10 
11 
12d4M4M/1Y/5Y
131Y 
145Y 
Sheet890
Cell Formulas
RangeFormula
D2:D14D2=IF(A2<>"",SUBSTITUTE(TRIM(B2&" "&B3&" "&B4)," ","/"),"")
 
Upvote 0

Forum statistics

Threads
1,212,931
Messages
6,110,745
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top