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

imran1059

Board Regular
Joined
Sep 28, 2014
Messages
92
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 DColumn LColumn M
0000000Aug salary of Eid A.Al-Aug salary of Eid A.Al- Qahtani
Qahtani
0589466SEP SALARY OF A.yunusSEP SALARY OF A.yunus MOHD NOOR
MOHD NOOR
0874896SEPT SALARY OF EID.A.AL-SEPT SALARY OF EID.A.AL- Harbi
Harbi
0000000SALARY OF MAHBOUB AL-SALARY OF MAHBOUB AL- Uddin
Uddin
0158933INTERNET ACCESS.INTERNET ACCESS.
0000000INTERNET LAN SERVICE.INTERNET LAN SERVICE.
0000000PETTY CASH FUND.PETTY CASH FUND.
0000000MOBILE TEL. FEES EID ALMOBILE TEL. FEES EID AL Dosairi
Dosairi
0000000MERDIAN NORSTARMERDIAN NORSTAR TEL.SYSTEM P15896 & P 18976 for transfer to B Star
TEL.SYSTEM P15896 & P
18976 for transfer to B
Star
0666492HP PRINTERS COMPAQHP PRINTERS COMPAQ PC.WINDOWS
PC.WINDOWS
te: 04-FEB-2018 11:38
ge: 3

Summaized Description
-------------------------
07798666Settlement ClaimsSettlement Claims of john bros
of john bros

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

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
test the following codes on a copy of your workbook
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,350
Messages
5,443,953
Members
405,258
Latest member
daveyf

This Week's Hot Topics

Top