How to concatenate several cells

Hello,

Please have a look of the attached. On the Tab called DataImport, I have to concatenate several cells that have the same serial number for example for 87319TOILETMD, I need to concatenate cells J1,J2 and J3. I know how to do that but the issue is if you look at the column A, we have 2 blanks seperating A1 from A4 and only one cell seperating A4 to A6 it is the same scenario throughout the entire column A . How can I can concatenate efficiently in this condition? Please help! Thanks

The end result that I am looking for is located in the Tab called Sheet13 for information. I would really appreciate if someone can help me with this issue

=CONCATENATE(A1,A4,A6)

Try this..

Click the Button on the DataImport sheet.. result shown on a new sheet i added (Sheet1).

Code:

---

Private Sub CommandButton1_Click()
    Dim x, xx, z, cnt As Long, i As Long, ii As Long
    x = [A1].CurrentRegion: xx = [A1].CurrentRegion.Columns(1).SpecialCells(2).Count: cnt = 1
    ReDim z(1 To xx, 1 To 10)
    For i = LBound(x) To UBound(x)
        If x(i, 1) <> "" Then
            If i > 1 Then cnt = cnt + 1
            For ii = LBound(x, 2) To UBound(x, 2)
                z(cnt, ii) = x(i, ii)
            Next ii
        Else
            z(cnt, 10) = z(cnt, 10) & " " & x(i, 10)
        End If
    Next i
    Sheets("Sheet1").[A2].Resize(UBound(z), 10).Value = z
End Sub

---

It is very useful. Thanks