How do I Concatenate these Dynamic Rows

[AwkSed2Excel]

I have the following data that I am unable to automatically concatenate
because the data is always dynamic. I import this data from a text
file and I need to get "For example:" and the rows belonging to "For
example:" into one cell in a column called Example.
Here is the data with four of the varying ranges.

For example:
G026: /etc/default/login does not specify 'CONSOLE=/dev/console',
'/dev/null', or 'none'.

For example:
CRITICAL: Security Patch(es) Not Installed: (16).
CRITICAL: Recommended Patch(es) Not Installed: (16).
CRITICAL: Y2K Patch(es) Not Installed: (0).
CRITICAL: Installed Recommended Patch(es) That Are Outdated: (148).
OPTIONAL: System Software Patch(es) Not Installed/Outdated: (111).
cp6 Has (183) Missing/Old Critical Patch(es)
cp6 Has (111) Missing/Old System Software Patch(es).

For example:
-rw-rw-r-- 1 bin bin 5560 Nov 9 1998 /usr/dt/share/man/man1/xmbind.1x

For example:
-rw-rw-r-- 1 root bin 11592 Dec 7 1999 /usr/lib/libsx.so.1
-rwxrwxr-x 1 root bin 3244 Dec 3 1999
/usr/openwin/lib/locale/iso_8859_1/libs/wckind.so.0
-rwxrwxr-x 1 bin bin 103904 Nov 5 1999
/usr/openwin/lib/xil/utils/xiliUtils.so.2
-rwxrwxr-x 1 bin bin 20656 Nov 5 1999
/usr/openwin/lib/xil/devhandlers/xilCompute_SUNWkcms.so.2
-rwxrwxr-x 1 bin bin 69068 Nov 5 1999
/usr/openwin/lib/xil/devhandlers/xilIO_SUNWcg6.so.2
-rwxrwxr-x 1 bin bin 51972 Nov 5 1999
/usr/openwin/lib/xil/devhandlers/xilIO_SUNWxlib.so.2
-rwxrwxr-x 1 bin bin 36692 Nov 5 1999
/usr/openwin/lib/xil/devhandlers/xilIO_SUNWxshm.so.2
-rwxrwxr-x 1 root bin 36452 Dec 7 1999
/usr/openwin/lib/xil/devhandlers/xilIO_SUNWcg14.so.2
-rwxrwxr-x 1 root bin 1062312 Dec 7 1999
/usr/openwin/lib/xil/devhandlers/xilCompute_VIS.so.2
-rwxrwxr-x 1 root bin 1168428 Dec 7 1999
/usr/openwin/lib/xil/devhandlers/xilIO_SUNWffb.so.2

 

[duane]

try this macro - limited at this time to 20 sets of string to
concatenate

Sub Macro1()
'Sheet1 has the raw, unconcatenated data
'Sheet2 gets the concatenated data
'istring(i) are the concatenated strings
Dim istring(20) As String
erow = 0
i = 1
start:
erow = erow + 1
Set rng = Sheets("sheet1").Range(Cells(erow, 1), Cells(1000,
1)).Find("For Example:")
If rng Is Nothing Then GoTo done
irow = rng.Row
erow = Cells(irow, 1).End(xlDown).Row
istring(i) = "For example: "
For j = irow To erow
istring(i) = istring(i) & Cells(j, 1).Value
Next j
i = i + 1
If i = 5 Then GoTo done
GoTo start
done:
Sheets("sheet2").Select
Cells(1, 1) = "Example"
For k = 1 To i - 1
Cells(k + 1, 1) = istring(k)
Next k
End Sub

 

[duane]

Note - the statement

If i = 5 Then GoTo done


should be removed - I placed it there to avoid an endless loop during
testing - sorry about that.

 

[AwkSed2Excel]

Thank you Duane! This is exactly what I needed.
Incidentally it works fine on my system at work, but not at home. Whe
I run the Macro at home, the first “For Example” and subsequent row i
omitted, but when I brought it to work this morning it worked jus
fine. Not a problem though, I’ll try to figure this one out on my own
Your example was far outside of the variations I was attempting, so yo
taught me another approach. Thanks Again

 

[duane]

You are quite welcome. This version has some minor improvements.

Sub Macro1()
'Sheet1 has the raw, unconcatenated data
'Sheet2 gets the concatenated data
'istring(i) are the concatenated strings
Sheets("sheet1").Select
Dim istring(20) As String
erow = 0
i = 1
start:
erow = erow + 1
Set rng = Sheets("sheet1").Range(Cells(erow, 1), Cells(1000,
1)).Find("For Example:")
If rng Is Nothing Then GoTo done
irow = rng.Row
erow = Cells(irow, 1).End(xlDown).Row
istring(i) = ""
For j = irow To erow
If j = irow Then istring(i) = Cells(j, 1).Value Else _
istring(i) = istring(i) & " " & Cells(j, 1).Value
Next j
i = i + 1
GoTo start
done:
Sheets("sheet2").Select
Cells(1, 1) = "Example"
For k = 1 To i - 1
Cells(k + 1, 1) = istring(k)
Next k
End Sub

 

[AwkSed2Excel]

Duane,
Your Macro worked great until I was thrown a new curve. The example I
posted above concatenates flawlessly until I add data from the real
text file (pasted below). I had been using individual recorded macros
to separate the rest of the data but tried to use this new method you
showed me all at once. Now I’m even more lost. Here is the data and
what I’m trying to accomplish:
Raw Data:
==========PDI=G004 Result==========
PDI Number: G004
Finding Category: CAT II
Reference: UNIX STIG: 3.2.1
Description: Passwords can be changed
For example:
alberaz:*LK*:::::::
==========PDI=G015 Result==========
PDI Number: G015
Finding Category: CAT II
Reference: UNIX STIG: 3.1.3
For example:
The SLEEPTIME variable is not set in
==========PDI=G018 Result============
…

Manual End Result (table; top row is column heading)
|PDI Number|-------|Finding
Category|------|Reference|------|Description---|------|For Example|
|G004--------|-------|CAT II------------|------|UNIX STIG|-----|An
Account ...|------|alberaz:*LK*:::::::|

 

[duane]

Hi, I am not sure I quite understand the layout of your raw data, an
desired output. Is it now every new "section" starts with a lin
beginning with this?

==========PDI

And then you want

PDI Number in column A, Finding Category, Reference, Description i
columns B-D,
and then the For example concatenation in column E

 

[AwkSed2Excel]

Thank you, yes, every new section starts with ======PDI, and the column
you mentioned are what I'm seeking. In actuality, the original raw tex
starts with from 10 to 50 (varying) lines of info-text that lead up t
the first "======PDI". As an aside, I wrote the following macro t
delete those lines.
______________________________
Public Sub deleterows()
' deletes the headers leading up to the first =====PDI=====
Dim selection As range
Set selection = Cells.Find(what:="PDI", lookat:=xlPart)
range("a1").EntireRow.Select
Do Until ActiveCell.Value = selection
ActiveCell.EntireRow.Delete
Loop
End Sub
_________________________________