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

3Bridges Community Before School Care, After School Care and Vacation Care <tbody> </tbody>	After School Hours Care <tbody> </tbody>
3Bridges Community Before School Care, After School Care and Vacation Care <tbody> </tbody>	Before School Hours Care <tbody> </tbody>
3Bridges Community Before School Care, After School Care and Vacation Care <tbody> </tbody>	Vacation Care <tbody> </tbody>
3C Kidz Care - Maranatha <tbody> </tbody>	Occasional Care <tbody> </tbody>
3C Kidz Care - Maranatha <tbody> </tbody>	Long Day Care <tbody> </tbody>
3C Kidz Care - Maranatha <tbody> </tbody>	After School Hours Care <tbody> </tbody>
3C Kidz Care - Maranatha <tbody> </tbody>	Before School Hours Care <tbody> </tbody>
A1 Fun Greenfields <tbody> </tbody>	After School Hours Care <tbody> </tbody>
A1 Fun Greenfields <tbody> </tbody>	Vacation Care <tbody> </tbody>
After The Bell Aus- Eastwood PS <tbody> </tbody>	Vacation Care

<tbody>
</tbody>

Post Merge

Organiztn	Type
3Bridges Community Before School Care, After School Care and Vacation Care	After School Hours Care, Before School Hours Care, Vacation Care
3C Kidz Care - Maranatha	Occasional Care, Long day care, After School Hours Care, Before School Hours Care
A1 Fun Greenfields	After School Hours Care, Vacation Care
After The Bell Aus- Eastwood PS	Vacation Care

<tbody>
</tbody>

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