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.

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

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[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