Merge rows

[Blessy Clara]

Hi All,

I have a list of records

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="width: 99, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 641"]
<tbody>[TR]
[TD="width: 641"]Youth Services[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="width: 99, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 641"]
<tbody>[TR]
[TD="width: 641"]Family / Whanau Services[/TD]
[/TR]
[TR]
[TD="width: 641"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="width: 99, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 641"]
<tbody>[TR]
[TD="width: 641"]Family Violence[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="width: 99, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 641"]
<tbody>[TR]
[TD="width: 641"]Education and Training[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="width: 99, align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 641"]
<tbody>[TR]
[TD="width: 641"]Family / Whanau Services[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="width: 99, align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 641"]
<tbody>[TR]
[TD="width: 641"]Basic Needs[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="width: 99, align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 641"]
<tbody>[TR]
[TD="width: 641"]Education and Training[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="width: 99, align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 641"]
<tbody>[TR]
[TD="width: 641"]Family / Whanau Services[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 99"]
<tbody>[TR]
[TD="width: 99, align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 641"]
<tbody>[TR]
[TD="width: 641"]Health[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I would like to merge rows based on the Index number into Single Row as given below

[TABLE="width: 500"]
<tbody>[TR]
[TD]3[/TD]
[TD][TABLE="width: 641"]
<tbody>[TR]
[TD="width: 641"]Youth Services, Family / Whanau Services, Family Violence, Education and Training[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 641"]
<tbody>[TR]
[TD="width: 641"]Family / Whanau Services, Basic Needs[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Family / Whanau Services, Health[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you

 

[footoo]

Are there any blank rows within the data? If yes, is it OK to delete those rows?

 

[Blessy Clara]

Footoo - Thank you for your response, Nope Blank rows.

 

[footoo]

Here's a macro based upon built-in XL tools and functions :

Sub IMerge()
Dim rng As Range
Application.ScreenUpdating = False
On Error Resume Next
Range([A2], Cells(Rows.Count, "A").End(xlUp)) _
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Set rng = Range([A2], Cells(Rows.Count, "A").End(xlUp))
rng.Offset(0, 2).Formula = "=IF(A2<>A3,0,""d"")"
rng.Offset(0, 3).Formula = "=IF(A2<>A1,B2,D1 & "", "" & B2)"
With rng.Offset(0, 2).Resize(, 2)
    .Value = .Value
End With
On Error Resume Next
rng.Offset(0, 2).SpecialCells(xlCellTypeConstants, 2).EntireRow.Delete
On Error GoTo 0
[B:C].Delete
End Sub

 

[footoo]

Cleaned-up a bit :

Sub IMerge()
Dim rng As Range
Application.ScreenUpdating = False
On Error Resume Next
Range([A2], Cells(Rows.Count, "A").End(xlUp)) _
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Set rng = Range([A2], Cells(Rows.Count, "A").End(xlUp)).Offset(0, 2)
rng.Formula = "=IF(A2<>A3,0,""d"")"
rng.Offset(0, 1).Formula = "=IF(A2<>A1,B2,D1 & "", "" & B2)"
With rng.Resize(, 2)
    .Value = .Value
End With
On Error Resume Next
rng.SpecialCells(xlCellTypeConstants, 2).EntireRow.Delete
On Error GoTo 0
[B:C].Delete Shift:=xlToLeft
End Sub

 

[Blessy Clara]

Wow, Thanks alot. It is very helpful

 

[Mike Szczesny]

Can you use this? I created a simple example from your format. Assume your number range is A1:A7, your services range is B1:B7, Assume Number field is in C1. Assume Description field is in D1. Put formula to find unique items in C2. Use Cntrl+Shift+Enter and copy down. This formula is [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =INDEX($A$1:$A$7,MATCH(0,COUNTIF($C$1:C1,$A$1:$A$7),0))
Place formula for numbers in D2. This formula first concatenates the descriptions matching a given number. The concatenation formula will produce FALSE for those numbers that do not match. Then I use a substitute formula to remove the "FALSE" results with "". [TABLE="width: 78"]
<tbody>[TR]
[TD="width: 78"]
This formula is below. Before entering you need to highlight the transpose part of the formula all the way to the second ). Highlight wit your mouse. Hit F9. The remove both { and }. Hit enter. Because of this additional step, you will not be able to copy down.
=substitute(concatenate(transpose(if($A$1:$A$7=$C$2,$B$1:$B$7&" / "))),"FALSE","")[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 270"]
<colgroup><col span="3"><col></colgroup><tbody>[TR]
[TD]3[/TD]
[TD]a[/TD]
[TD]Number[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]b[/TD]
[TD="align: right"]3[/TD]
[TD]a / b / c / [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]d[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]e[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]f[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]g[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Blessy Clara]

Mike Szczesny - Thank you for your efforts and time,