Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I Concatenate these Dynamic Rows
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 -- AwkSed2Excel ------------------------------------------------------------------------ AwkSed2Excel's Profile: http://www.excelforum.com/member.php...o&userid=23464 View this thread: http://www.excelforum.com/showthread...hreadid=374110 |
#2
|
|||
|
|||
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 ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=374110 |
#3
|
|||
|
|||
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. -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=374110 |
#4
|
|||
|
|||
Thank you Duane! This is exactly what I needed. Incidentally it works fine on my system at work, but not at home. When I run the Macro at home, the first “For Example” and subsequent row is omitted, but when I brought it to work this morning it worked just 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 you taught me another approach. Thanks Again. -- AwkSed2Excel ------------------------------------------------------------------------ AwkSed2Excel's Profile: http://www.excelforum.com/member.php...o&userid=23464 View this thread: http://www.excelforum.com/showthread...hreadid=374110 |
#5
|
|||
|
|||
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 -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=374110 |
#6
|
|||
|
|||
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*:::::::| -- AwkSed2Excel ------------------------------------------------------------------------ AwkSed2Excel's Profile: http://www.excelforum.com/member.php...o&userid=23464 View this thread: http://www.excelforum.com/showthread...hreadid=374110 |
#7
|
|||
|
|||
Hi, I am not sure I quite understand the layout of your raw data, and desired output. Is it now every new "section" starts with a line beginning with this? ==========PDI And then you want PDI Number in column A, Finding Category, Reference, Description in columns B-D, and then the For example concatenation in column E? -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=374110 |
#8
|
|||
|
|||
Thank you, yes, every new section starts with ======PDI, and the columns you mentioned are what I'm seeking. In actuality, the original raw text starts with from 10 to 50 (varying) lines of info-text that lead up to the first "======PDI". As an aside, I wrote the following macro to 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 __________________________________ -- AwkSed2Excel ------------------------------------------------------------------------ AwkSed2Excel's Profile: http://www.excelforum.com/member.php...o&userid=23464 View this thread: http://www.excelforum.com/showthread...hreadid=374110 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
Dynamic charting problems (events) | Charts and Charting in Excel | |||
Adding Rows to Master Sheet | New Users to Excel | |||
Dynamic number of ROWS | Excel Worksheet Functions |