Concatenate Cells To Consolidate Info in Cells

[edokhotnik]

I have a sheet that shows data output from a log. I would like to concatenate the cells in Column C and show all data for each category in one row.

Before VBA:

Excel 2010

	A	B	C	D	E	F
1	Type	Mode/Class	Details (Path/Message/Protocol/Hostname/Qtype/ListenPort etc.)	Process ID	Parent ID	File Size
2	Javacall		Method: setSecurityManager	2352
3			Params: [0x02B440B0]
4			Imagepath: C:\Program Files\Java\jre6\bin\java.exe
5	Javaevent
6	API Call		API Name: GetLocalTime Address: 0x6d6b12ed	2352
7			Params: [0x3dfadc]
8			Imagepath: C:\Program Files\Java\jre6\bin\java.exe DLL Name: kernel32.dll
9	File	Close	C:\Documents and Settings\admin\Local Settings\Temp\java_install_reg.log	2352		3374
10			MD5: ab73724687f184d7079b0347f372a143
11			SHA1: 37dd7a2cc8a36e0840c4780889965eb37f0017c4
12	Uac	Audit policy change
13	File	Close	C:\Documents and Settings\admin\Local Settings\Temp\java_install_reg.log	2352		3457
14			MD5: a10d853ce23f2e790e82a984830e3cd7
15			SHA1: 43809a426369606fecef81fd8e36a79d05f5a644
16	API Call		API Name: Sleep Address: 0x066a1326	2352
17			Imagepath: C:\Program Files\Java\jre6\bin\java.exe DLL Name: kernel32.dll
18	Malicious Alert	Misc Anomaly	Message: Tracking Sleep/SleepEx API Call Detail: Process Sleep
19	File	Overwritten	C:\Documents and Settings\admin\Application Data\Sun\Java\Deployment\deployment.properties	2352		1171
20			MD5: c0bd2a510a60d95c3e364c12e408bba0
21			SHA1: deef0343705d218398e18ed7b8177083f0e6544f
22	API Call		API Name: GetVersionExA Address: 0x6d33a797	2352
23			Imagepath: C:\Program Files\Java\jre6\bin\java.exe DLL Name: kernel32.dll

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

After VBA:

Excel 2010

	A	B	C	D	E	F
28	Type	Mode/Class	Details (Path/Message/Protocol/Hostname/Qtype/ListenPort etc.)	Process ID	Parent ID	File Size
29	Javacall		Method: setSecurityManager Params: [0x02B440B0] Imagepath: C:\Program Files\Java\jre6\bin\java.exe	2352
30	Javaevent
31	API Call		API Name: GetLocalTime Address: 0x6d6b12ed Params: [0x3dfadc] Imagepath: C:\Program Files\Java\jre6\bin\java.exe DLL Name: kernel32.dll	2352
32	File	Close	C:\Documents and Settings\admin\Local Settings\Temp\java_install_reg.log MD5: ab73724687f184d7079b0347f372a143 SHA1: 37dd7a2cc8a36e0840c4780889965eb37f0017c4	2352		3374
33	Uac	Audit policy change
34	File	Close	C:\Documents and Settings\admin\Local Settings\Temp\java_install_reg.log MD5: ab73724687f184d7079b0347f372a143 SHA1: 37dd7a2cc8a36e0840c4780889965eb37f0017c4	2352		3457
35	API Call		API Name: Sleep Address: 0x066a1326 Imagepath: C:\Program Files\Java\jre6\bin\java.exe DLL Name: kernel32.dll	2352
36	Malicious Alert	Misc Anomaly	Message: Tracking Sleep/SleepEx API Call Detail: Process Sleep
37	File	Overwritten	C:\Documents and Settings\admin\Application Data\Sun\Java\Deployment\deployment.properties MD5: c0bd2a510a60d95c3e364c12e408bba0 SHA1: deef0343705d218398e18ed7b8177083f0e6544f	2352		1171
38	API Call		API Name: GetVersionExA Address: 0x6d33a797 Imagepath: C:\Program Files\Java\jre6\bin\java.exe DLL Name: kernel32.dll	2352

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Thank you for your assistance.

 

[edokhotnik]

bump.

 

[Rick Rothstein]

Give this macro a try (test on a copy of your workbook to make sure it does what you want first)...

Sub ConsolidateData()
  Dim LastRow As Long, Blanks As Range, Ar As Range
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Set Blanks = Range("A1:A" & LastRow).SpecialCells(xlBlanks)
  For Each Ar In Blanks.Areas
    Ar.Offset(-1, 2)(1).Value = Application.Trim(Join(Application.Transpose(Ar.Offset(-1, 2).Resize(Ar.Count + 1))))
  Next
  Blanks.EntireRow.Delete
End Sub

 

[edokhotnik]

Thanks, Rick. That worked great. Much appreciated. Happy New Year!