Desperate: Take Imported Data from one column and TRANSPOSE? to multiple rows and columns to eventually CONCATENATE

[EJDJR]

Very desperate, appreciate any help I can get. Have a Tab delimited TXT file that imports as a single column. It represents a list of communication with some fields that repeat among all records and some fields that are unique. unfortunately the "Subject" cells sometimes gets broken into multiple cells so this cant be a simple Transpose every 15 rows. Below is a sample of my sheet.

The first 2 columns I added for identification and sorting. Each record Begins with a cell "BEGIN:VMSG" and ends with cell "END:VMSG". The hiccup for me is that after the 13 cell (the one that begins "Subject") there may be several more cells of random text or it may go straight to the "END:VBODY" (see difference between A13-A14 vs B13-B15. I would settle for being able to Transpose the cells starting with "BEGIN:VMSG" through "END:VMSG" but the ultimate goal is to combine all rows from 1 record into one cell so I can delete exact duplicates. I'm aware I need to do this across several sheets because my total rows exceeds Excel Limits. The TXT file contains about 1.5 million rows of which about 500,000 are duplicates.
I need to combine all these rows before finding duplicates because some subject cells contain the same text but are from different dates or contacts so are not exact duplicates.
Using Excel 2016 64-bit, also have KuTools and ASAP installed....and yes i'm pretty much a noob but am very comfortable adding a delimiter when combing andgoing back via Text to Columns, etc.
Thanks ahead of time

 

[yky]

So, you want to concatenate every thing between "BEGIN:VMSG" and "END:VMSG"? I have a non-VBA answer.

Because you posted an image, not text, I had to enter your data manually and my data started from Column A, without your Column A & B. In other words, you Column C is my Column A. I ignore your Column A & B.

There are only three steps:

1. Copy A1 to B1. (If using your image, it would be C1 to D1.)
2. Enter this formula from B2 down to the end of Column B: "=IF(A2="BEGIN:VMSG",A2, B1 & " " & A2)"
3. Enter this formula from C1 down: "=IF(RIGHT(B1,8)<>"END:VMSG","",B1)"

Column C contains either blanks or concatenated answer you are after. It works on your data of 31 rows. Not sure how fast it will be if you have millions of rows.

To enter a formula into multiple cells, do this:
1. Hit F5 and enter the range of the cells you want the formula to be in, e.g. B2:B10768,
2. Enter the formula in the address bar,
3. Hit Ctrl-Enter.

 

[EJDJR]

Holy crap, thank you so much this is perfect. I owe you a beer.

 

[Peter_SSs]

Welcome to the MrExcel board!

.. this is perfect.

I don't know how big a sample you have tried that on, but given that you are talking about more that a million rows of data I think you may find that formula method causes Excel to stop responding, or at least be extremely slow.

A macro approach should be much faster.
Assuming that you have split your data if necessary and have a block of it in column A of your worksheet & you want the results in column B, you could try the following (in a copy of your workbook).

Note that I do not have Excel 64-bit so I'm not sure if there are any issues associated with that when running my code.

1. If you want the results spread out down the sheet like the formula solution gave, then ..

Sub Concat_VMSG_v1()
  Dim a As Variant, b As Variant
  Dim s As String
  Dim i As Long
  
  With ActiveSheet
    a = Intersect(.UsedRange, .Columns("A")).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      Select Case a(i, 1)
        Case "BEGIN:VMSG"
          s = a(i, 1)
        Case "END:VMSG"
          b(i, 1) = s & " " & a(i, 1)
        Case Else
          s = s & " " & a(i, 1)
      End Select
    Next i
    .Range("B1").Resize(UBound(b)).Value = b
  End With
End Sub

2. If you would prefer the results listed in consecutive rows in column B, then ..

Sub Concat_VMSG_v2()
  Dim a As Variant, b As Variant
  Dim s As String
  Dim i As Long, k As Long
  
  With ActiveSheet
    a = Intersect(.UsedRange, .Columns("A")).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      Select Case a(i, 1)
        Case "BEGIN:VMSG"
          s = a(i, 1)
        Case "END:VMSG"
          k = k + 1
          b(k, 1) = s & " " & a(i, 1)
        Case Else
          s = s & " " & a(i, 1)
      End Select
    Next i
    .Range("B1").Resize(k).Value = b
  End With
End Sub

BTW, if asking future questions, you will get many more potential helpers if you provide sample data directly in your post in a forum that can be copied/pasted for testing.
See Attachments