Concatenate Cells To Consolidate Info in Cells

edokhotnik

Board Regular
Joined
Nov 10, 2010
Messages
104
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
ABCDEF
1TypeMode/ClassDetails (Path/Message/Protocol/Hostname/Qtype/ListenPort etc.)Process IDParent IDFile Size
2JavacallMethod: setSecurityManager2352
3 Params: [0x02B440B0]
4 Imagepath: C:\Program Files\Java\jre6\bin\java.exe
5Javaevent
6API Call API Name: GetLocalTime Address: 0x6d6b12ed2352
7 Params: [0x3dfadc]
8 Imagepath: C:\Program Files\Java\jre6\bin\java.exe DLL Name: kernel32.dll
9FileCloseC:\Documents and Settings\admin\Local Settings\Temp\java_install_reg.log23523374
10 MD5: ab73724687f184d7079b0347f372a143
11 SHA1: 37dd7a2cc8a36e0840c4780889965eb37f0017c4
12UacAudit policy change
13FileCloseC:\Documents and Settings\admin\Local Settings\Temp\java_install_reg.log23523457
14 MD5: a10d853ce23f2e790e82a984830e3cd7
15 SHA1: 43809a426369606fecef81fd8e36a79d05f5a644
16API Call API Name: Sleep Address: 0x066a13262352
17 Imagepath: C:\Program Files\Java\jre6\bin\java.exe DLL Name: kernel32.dll
18Malicious AlertMisc AnomalyMessage: Tracking Sleep/SleepEx API Call Detail: Process Sleep
19FileOverwrittenC:\Documents and Settings\admin\Application Data\Sun\Java\Deployment\deployment.properties23521171
20 MD5: c0bd2a510a60d95c3e364c12e408bba0
21 SHA1: deef0343705d218398e18ed7b8177083f0e6544f
22API Call API Name: GetVersionExA Address: 0x6d33a7972352
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
ABCDEF
28TypeMode/ClassDetails (Path/Message/Protocol/Hostname/Qtype/ListenPort etc.)Process IDParent IDFile Size
29JavacallMethod: setSecurityManager Params: [0x02B440B0] Imagepath: C:\Program Files\Java\jre6\bin\java.exe2352
30Javaevent
31API Call API Name: GetLocalTime Address: 0x6d6b12ed Params: [0x3dfadc] Imagepath: C:\Program Files\Java\jre6\bin\java.exe DLL Name: kernel32.dll2352
32FileCloseC:\Documents and Settings\admin\Local Settings\Temp\java_install_reg.log MD5: ab73724687f184d7079b0347f372a143 SHA1: 37dd7a2cc8a36e0840c4780889965eb37f0017c423523374
33UacAudit policy change
34FileCloseC:\Documents and Settings\admin\Local Settings\Temp\java_install_reg.log MD5: ab73724687f184d7079b0347f372a143 SHA1: 37dd7a2cc8a36e0840c4780889965eb37f0017c423523457
35API Call API Name: Sleep Address: 0x066a1326 Imagepath: C:\Program Files\Java\jre6\bin\java.exe DLL Name: kernel32.dll2352
36Malicious AlertMisc AnomalyMessage: Tracking Sleep/SleepEx API Call Detail: Process Sleep
37FileOverwrittenC:\Documents and Settings\admin\Application Data\Sun\Java\Deployment\deployment.properties MD5: c0bd2a510a60d95c3e364c12e408bba0 SHA1: deef0343705d218398e18ed7b8177083f0e6544f23521171
38API Call API Name: GetVersionExA Address: 0x6d33a797 Imagepath: C:\Program Files\Java\jre6\bin\java.exe DLL Name: kernel32.dll2352

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



Thank you for your assistance.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,187
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Give this macro a try (test on a copy of your workbook to make sure it does what you want first)...

Code:
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
 

Forum statistics

Threads
1,137,060
Messages
5,679,380
Members
419,824
Latest member
Mercy kiara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top