EXCEL VBA To combine cells into one seperated by

Hello,
Can you please help with an Excel  macro.
Column "A",
if the cell is called Tracking ID
Then, I need to combine all cells after the  Tracking ID cell into the first next row cell.
until the cell in Column "C" has a name which starts with QA:
and clear cells.

Please see sample attached sample, before, after.

Thanks,
W.E.BAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Sub qa2qaMerge()
    Dim tid As Range, nxtcel As Range, qacel As Range
    Set tid = Cells.Find("Tracking ID", Cells.SpecialCells(xlCellTypeLastCell), , xlWhole).Offset(1)
    Set nxtcel = Range("A1")
    Set qacel = nxtcel
    Do While tid.Row > nxtcel.Row And tid.Row > qacel.Row
        tid.Select
        tid.Value = "TRID: " & tid.Text
        tid.HorizontalAlignment = xlGeneral
        Set qacel = Range(tid, Cells.SpecialCells(xlCellTypeLastCell)).Find("QA:", tid, , xlPart)
        Set nxtcel = Cells.Find("*", tid)
        nxtcel.Select
        Do
            If nxtcel.Row < tid.Row Then Exit Do
            If Not qacel Is Nothing Then
                If nxtcel.Row >= qacel.Row Then Exit Do
            End If
            If Left(nxtcel, 3) = "QA:" Then Exit Do
                tid.Value = tid.Text & ", " & nxtcel.Text
                nxtcel.ClearContents
            Set nxtcel = Cells.Find("*", nxtcel)
            nxtcel.Select
        Loop
        If qacel Is Nothing Then Exit Do
        Set tid = Cells.Find("Tracking ID", qacel, , xlWhole).Offset(1)
    Loop
End Sub
0
 
Saqib Husain, SyedEngineerCommented:
Sample?
0
 
W.E.BAuthor Commented:
Ooooops.
Sample.xlsx
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Saqib Husain, SyedEngineerCommented:
Sub qa2qaMerge()
    Dim tid As Range, nxtcel As Range
    Set tid = Cells.Find("Tracking ID", Cells.SpecialCells(xlCellTypeLastCell), , xlWhole).Offset(1)
    Set nxtcel = Range("A1")
    Do While tid.Row > nxtcel.Row
        tid.Value = "TRID: " & tid.Text
        tid.HorizontalAlignment = xlGeneral
        Set nxtcel = Cells.Find("*", tid)
        Do While nxtcel.Row >= tid.Row
            If Left(nxtcel, 3) = "QA:" Then Exit Do
                tid.Value = tid.Text & ", " & nxtcel.Text
                nxtcel.ClearContents
            Set nxtcel = Cells.Find("*", nxtcel)
        Loop
        Set tid = Cells.Find("Tracking ID", nxtcel, , xlWhole).Offset(1)
    Loop
End Sub
0
 
W.E.BAuthor Commented:
Hi Saqib,
Appreciate your help,
I'm trying the code on about 1000 Rows,
it just keeps looping and doesn't stop.

thanks
0
 
Saqib Husain, SyedEngineerCommented:
Can you share the file?
0
 
W.E.BAuthor Commented:
Please see attached sample
Sample.xlsx
0
 
Saqib Husain, SyedEngineerCommented:
This file is very much different from the previous sample.

The infinite loop is because there is no QA: after the last Tracking ID.

Do you want to do it this way?
0
 
W.E.BAuthor Commented:
yes, please
thank you very much
0
 
W.E.BAuthor Commented:
Awesome Saqib.
Thank you very VERY much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.