Check a cell in one column and concatenate the rows in another column and ignore some cells.

[imran1059]

I need some formula or vba code to get the desired output. Formula should check column D if a cell in column D starts with "0" then concatenate all rows in column L (with spaces) until another cell in column D that starts with "0". Red highlighted cells are the garbage that are produced by the report we need to ignore them they will come at the start of every page and I have thousands of pages.

[TABLE="width: 726"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]Given Column
[/TD]
[TD="align: left"]Given Column
[/TD]
[TD="align: left"]Desired Output[/TD]
[/TR]
[TR]
[TD="align: left"]Column D[/TD]
[TD="align: left"]Column L[/TD]
[TD="align: left"]Column M
[/TD]
[/TR]
[TR]
[TD="align: left"]0000000[/TD]
[TD="align: left"]Aug salary of Eid A.Al-[/TD]
[TD="align: left"]Aug salary of Eid A.Al- Qahtani
[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: left"]Qahtani[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: left"]0589466[/TD]
[TD="align: left"]SEP SALARY OF A.yunus[/TD]
[TD="align: left"]SEP SALARY OF A.yunus MOHD NOOR[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: left"]MOHD NOOR[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]0874896[/TD]
[TD="align: left"]SEPT SALARY OF EID.A.AL-[/TD]
[TD="align: left"]SEPT SALARY OF EID.A.AL- Harbi[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: left"]Harbi[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]0000000[/TD]
[TD="align: left"]SALARY OF MAHBOUB AL-[/TD]
[TD="align: left"]SALARY OF MAHBOUB AL- Uddin[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: left"]Uddin[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]0158933[/TD]
[TD="align: left"]INTERNET ACCESS.[/TD]
[TD="align: left"]INTERNET ACCESS.[/TD]
[/TR]
[TR]
[TD="align: left"]0000000[/TD]
[TD="align: left"]INTERNET LAN SERVICE.[/TD]
[TD="align: left"]INTERNET LAN SERVICE.[/TD]
[/TR]
[TR]
[TD="align: left"]0000000[/TD]
[TD="align: left"]PETTY CASH FUND.[/TD]
[TD="align: left"]PETTY CASH FUND.[/TD]
[/TR]
[TR]
[TD="align: left"]0000000[/TD]
[TD="align: left"]MOBILE TEL. FEES EID AL[/TD]
[TD="align: left"]MOBILE TEL. FEES EID AL Dosairi[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: left"]Dosairi[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]0000000[/TD]
[TD="align: left"]MERDIAN NORSTAR[/TD]
[TD="align: left"]MERDIAN NORSTAR TEL.SYSTEM P15896 & P 18976 for transfer to B Star[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: left"]TEL.SYSTEM P15896 & P[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]18976 for transfer to B[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: left"]Star[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]0666492[/TD]
[TD="align: left"]HP PRINTERS COMPAQ[/TD]
[TD="align: left"]HP PRINTERS COMPAQ PC.WINDOWS[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: left"]PC.WINDOWS[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: left"]te: 04-FEB-2018 11:38
[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: left"]ge: 3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: left"]Summaized Description[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: left"]-------------------------
[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: left"]07798666[/TD]
[TD="align: left"]Settlement Claims[/TD]
[TD="align: left"]Settlement Claims of john bros[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: left"]of john bros[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[ravishankar]

test the following codes on a copy of your workbook

Sub Imran()
Dim a As Integer, b As Integer
Dim join As String
a = Cells(Rows.Count, 4).End(xlUp).Row
For b = 1 To a
    If InStr(1, Cells(b, 4), "0") > 0 Then
    join = ""
        If Cells(b, 12).Font.ColorIndex <> 3 Then
        join = join & "  " & Cells(b, 12)
        Cells(b, 13) = join
        Else
        join = join & "  " & Cells(b, 12)
        Cells(b, 13) = join
        End If
    End If
Next b
MsgBox "complete"
End Sub

ravishankar