Merge Rows, if

[Blessy Clara]

Hi All,

I have an excel sheet with two Columns, Organization Name and Type. I would like to merge all the types for a particular organization into one single row.

Example

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 525"]
<tbody>[TR]
[TD="class: xl63, width: 525"]3Bridges Community Before School Care, After School Care and Vacation Care[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 198"]
<tbody>[TR]
[TD="class: xl65, width: 198"]After School Hours Care[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 525"]
<tbody>[TR]
[TD="class: xl65, width: 525"]3Bridges Community Before School Care, After School Care and Vacation Care[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 198"]
<tbody>[TR]
[TD="class: xl65, width: 198"]Before School Hours Care[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 525"]
<tbody>[TR]
[TD="class: xl65, width: 525"]3Bridges Community Before School Care, After School Care and Vacation Care[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 198"]
<tbody>[TR]
[TD="class: xl65, width: 198"]Vacation Care[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 525"]
<tbody>[TR]
[TD="class: xl65, width: 525"]3C Kidz Care - Maranatha[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD="class: xl65, width: 160"]Occasional Care[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 525"]
<tbody>[TR]
[TD="class: xl65, width: 525"]3C Kidz Care - Maranatha[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD="class: xl65, width: 160"]Long Day Care[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 525"]
<tbody>[TR]
[TD="class: xl65, width: 525"]3C Kidz Care - Maranatha[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD="class: xl65, width: 160"]After School Hours Care[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 525"]
<tbody>[TR]
[TD="class: xl65, width: 525"]3C Kidz Care - Maranatha[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 160"]
<tbody>[TR]
[TD="class: xl65, width: 160"]Before School Hours Care[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 525"]
<tbody>[TR]
[TD="class: xl65, width: 525"]A1 Fun Greenfields[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 198"]
<tbody>[TR]
[TD="class: xl65, width: 198"]After School Hours Care[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 525"]
<tbody>[TR]
[TD="class: xl65, width: 525"]A1 Fun Greenfields[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 198"]
<tbody>[TR]
[TD="class: xl65, width: 198"]Vacation Care[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 525"]
<tbody>[TR]
[TD="class: xl65, width: 525"]After The Bell Aus- Eastwood PS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Vacation Care[/TD]
[/TR]
</tbody>[/TABLE]

Post Merge

[TABLE="width: 500"]
<tbody>[TR]
[TD]Organiztn[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]3Bridges Community Before School Care, After School Care and Vacation Care[/TD]
[TD]After School Hours Care, Before School Hours Care, Vacation Care[/TD]
[/TR]
[TR]
[TD]3C Kidz Care - Maranatha[/TD]
[TD]Occasional Care, Long day care, After School Hours Care, Before School Hours Care[/TD]
[/TR]
[TR]
[TD]A1 Fun Greenfields[/TD]
[TD]After School Hours Care, Vacation Care[/TD]
[/TR]
[TR]
[TD]After The Bell Aus- Eastwood PS[/TD]
[TD]Vacation Care[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for any help

 

[James006]

Hello,

Never merge cells within Excel ... !!!

Excel is not a common word processor ...

You could use some concatenation ... to achieve your goal ...

 

[Blessy Clara]

Hi Thank you for your suggestion, one of my previous posts, i had a similar scenario, just in that it was numeric and worked awesome

https://www.mrexcel.com/forum/excel-questions/1039433-merge-rows.html

Since there are close to 30,0000 data as this, looking out for some option, as Manual is very tough

 

[Fluff]

How about

Sub MergeData()

   Dim Cl As Range
   Dim Dic As Object
   Dim Ky As Variant
   
   Set Dic = CreateObject("scripting.dictionary")
   With Dic
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Cl.Offset(, 1).Value
         Else
            .Item(Cl.Value) = .Item(Cl.Value) & ", " & Cl.Offset(, 1).Value
         End If
      Next Cl
      For Each Ky In .keys
         With Range("D" & Rows.Count).End(xlUp)
            .Offset(1).Value = Ky
            .Offset(1, 1).Value = Dic(Ky)
         End With
      Next Ky
   End With
   
End Sub

I've assumed that your data is in cols A:B starting in row 2.
This will give the output in col D:E