Merging records grouped by variable

y3ll3r · Mar 18, 2009

Hi I have this table:

1 A
1 B
1 C
2 D
2 E
3 F
3 G

I want the 2nd column to be merged with a line break making the table look like this:

A
1 B
C
2 D
E
3 F
G

So the values ABC are all in the same cell, separated by a line break and on the same level as 1.

Can anyone advise on how to do something like this with VBA/ Macro's??

Any help would be much appreciated.
Cheers,

y3ll3r

Krishnakumar · Mar 18, 2009

Hi,

Code:

Sub kTest()
Dim i As Long, r As Long
i = Range("a" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = 0
Application.DisplayAlerts = 0
For r = i To 2 Step -1
    If Cells(r, 1) = Cells(r - 1, 1) Then Cells(r - 1, 1).Resize(2).Merge
Next
Application.ScreenUpdating = 1
Application.DisplayAlerts = 1
End Sub

HTH

y3ll3r · Mar 18, 2009

Hi Kris,

Thanks for your help, it is not yet completely what I need, because the info column 2 needs to be merged into one cell as well, like using the CHAR(10) code.

So it would be something like

1 A & Char(10) & B & CHAR (10) & C

This would end having all information in one row.

Cheers,

y3ll3r

y3ll3r · Mar 18, 2009

<table style="border-collapse: collapse; width: 180pt;" width="238" border="0" cellpadding="0" cellspacing="0"><col style="width: 32pt;" width="42"> <col style="width: 34pt;" width="45"> <col style="width: 53pt;" width="70"> <col style="width: 61pt;" width="81"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl63" style="height: 12.75pt; width: 32pt;" width="42" height="17">URN</td> <td class="xl64" style="border-left: medium none; width: 34pt;" width="45">NAME</td> <td class="xl64" style="border-left: medium none; width: 53pt;" width="70">ADDRESS</td> <td class="xl64" style="border-left: medium none; width: 61pt;" width="81">ADDRESS-2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt;" align="right" height="17">30941</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Z</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">11</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt;" align="right" height="17">30941</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">A</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Z</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">22</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt;" align="right" height="17">1094</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">X</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">33</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt;" align="right" height="17">1094</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">X</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">44</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="border-top: medium none; height: 12.75pt;" align="right" height="17">1094</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">B</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">X</td> <td class="xl66" style="border-top: medium none; border-left: medium none;">55</td> </tr> </tbody></table>
<table style="border-collapse: collapse; width: 180pt;" width="238" border="0" cellpadding="0" cellspacing="0"><col style="width: 32pt;" width="42"> <col style="width: 34pt;" width="45"> <col style="width: 53pt;" width="70"> <col style="width: 61pt;" width="81"> <tbody><tr style="height: 25.5pt;" height="34"> <td class="xl65" style="height: 25.5pt; width: 32pt;" width="42" align="right" height="34">30941</td> <td class="xl66" style="border-left: medium none; width: 34pt;" width="45">A
A</td> <td class="xl66" style="border-left: medium none; width: 53pt;" width="70">Z
Z</td> <td class="xl66" style="border-left: medium none; width: 61pt;" width="81">11
22</td> </tr> <tr style="height: 38.25pt;" height="51"> <td class="xl65" style="border-top: medium none; height: 38.25pt;" align="right" height="51">1094</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 34pt;" width="45">B
B
B</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 53pt;" width="70">X
X
X</td> <td class="xl66" style="border-top: medium none; border-left: medium none; width: 61pt;" width="81">33
44
55</td> </tr> </tbody></table>
So AA is in one cell, BBB is in one cell instead of three different ones.
Hope this helps to clarify...

Krishnakumar · Mar 21, 2009

Hi,

Sorry for the late reply.

Try,

Code:

Sub kTest()
Dim a, txt As String, i As Long, r As Long

i = Range("a" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = 0
Application.DisplayAlerts = 0
For r = i To 2 Step -1
    If Cells(r, 1) = Cells(r - 1, 1) Then
        Cells(r - 1, 1).Resize(2).Merge
        Cells(r - 1, 2) = Cells(r - 1, 2) & Chr(10) & Cells(r, 2)
        Cells(r - 1, 2).Resize(2).Merge
    End If
Next
Application.ScreenUpdating = 1
Application.DisplayAlerts = 1
End Sub

HTH

y3ll3r · Mar 21, 2009

This is the code that finally worked for me:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 12"><meta name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CMICHIE%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CMICHIE%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CMICHIE%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman";} a:link, span.MsoHyperlink {mso-style-noshow:yes; mso-style-priority:99; color:blue; text-decoration:underline; text-underline:single;} a:visited, span.MsoHyperlinkFollowed {mso-style-noshow:yes; mso-style-priority:99; color

urple; mso-themecolor:followedhyperlink; text-decoration:underline; text-underline:single;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 72.0pt 72.0pt 72.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> Sub Shaastie_is_zo_slim()<o

></o

>
<o

> </o

>
Dim i As Integer<o

></o

>
Dim j As Integer<o

></o

>
Dim start As Long<o

></o

>
Dim next_int As Long<o

></o

>
Dim k As Integer<o

></o

>
Dim begin As Integer<o

></o

>
Dim m As Integer<o

></o

>
<o

> </o

>
<o

> </o

>
i = 20 'begin positie records<o

></o

>
n = 62 'eind positie aantal records<o

></o

>
m = 19 'eind positie van het aantal kolommen<o

></o

>
<o

> </o

>
<o

> </o

>
k = 0<o

></o

>
eind = 0<o

></o

>
<o

> </o

>
start = Sheets("Sheet1").Cells(i, 1)<o

></o

>
<o

></o

>
Do While i < n<o

></o

>
<o

></o

>
begin = i<o

></o

>
start = Sheets("Sheet1").Cells(i, 1)<o

></o

>
next_int = Sheets("Sheet1").Cells(i + 1, 1)<o

></o

>
<o

></o

>
Do While next_int = start<o

></o

>
eind = eind + 1<o

></o

>
i = i + 1<o

></o

>
next_int = Sheets("Sheet1").Cells(i + 1, 1)<o

></o

>
Loop<o

></o

>
<o

></o

>
If next_int <> start Then<o

></o

>
k = k + 1<o

></o

>
End If<o

></o

>
<o

></o

>
For j = 2 To m<o

></o

>
For x = begin To begin + eind<o

></o

>
If x = begin Then<o

></o

>
Sheets("Sheet2").Cells(k, j).Formula = Sheets("Sheet1").Cells(begin, j)<o

></o

>
Sheets("Sheet2").Cells(k, 1).Formula = Sheets("Sheet1").Cells(begin, 1)<o

></o

>
End If<o

></o

>
If x <> begin Then<o

></o

>
Sheets("Sheet2").Cells(k, j).Formula = Sheets("Sheet2").Cells(k, j) & Chr(10) & Sheets("Sheet1").Cells(x, j)<o

></o

>
End If<o

></o

>
Next<o

></o

>
Next<o

></o

>
i = i + 1<o

></o

>
eind = 0<o

></o

>
Loop<o

></o

>
<o

> </o

>
<o

></o

>
End Sub<o

></o

>
<o

> </o

>
<o

> </o

> <table class="MsoNormalTable" style="width: 507px; height: 139px;" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style=""> <td style="padding: 0cm;">
</td> </tr> <tr style=""> <td style="padding: 0cm;">
</td> </tr> <tr style=""> <td style="padding: 0cm;">
</td> </tr> <tr style=""> <td style="padding: 0cm;">
</td> </tr> <tr style=""> <td style="padding: 0cm;">
</td> </tr> </tbody></table>

Merging records grouped by variable

y3ll3r

New Member

Excel Facts

Krishnakumar

Well-known Member

y3ll3r

New Member

y3ll3r

New Member

Krishnakumar

Well-known Member

y3ll3r

New Member

Similar threads

Forum statistics

Share this page

Merging records grouped by variable

y3ll3r

New Member

Excel Facts

Krishnakumar

Well-known Member

y3ll3r

New Member

y3ll3r

New Member

Krishnakumar

Well-known Member

y3ll3r

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock