Merging cells based on same values

[Vasanth]

Hi

Below is my requirement

I have cells in a excel in the below format

AAA 12323
AAA 56565
AAA 77789
BBB 12
BBB 13
DDD 142
CCC 121
CCC 13


I need to merge the cell of column A until the value is same . My outpu
should be


12323
AAA 56565
77789

12
BBB 13

DDD 142

CCC 121
13

Please let me know if this can be achieved through some macros o
forula

Thanks
Vasant

 

[Ron Rosenfeld]

Hi

Below is my requirement

I have cells in a excel in the below format

AAA 12323
AAA 56565
AAA 77789
BBB 12
BBB 13
DDD 142
CCC 121
CCC 13


I need to merge the cell of column A until the value is same . My output
should be


12323
AAA 56565
77789

12
BBB 13

DDD 142

CCC 121
13

Please let me know if this can be achieved through some macros or
forula

Thanks
Vasanth

This Macro assumes your cells to be merged are in column A, starting in A1. If they start someplace else, change the initial
Set r = ...
statement to reflect the proper column.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

=============================================
Option Explicit
Sub MergeSame()
Dim r As Range, c As Range
Dim i As Long, j As Long
Set r = Range("A1", Cells(Rows.Count, "A").End(xlUp))

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For i = 1 To r.Count
Set c = r(i)
j = 0
Do Until c <> c.Offset(rowoffset:=1)
Set c = c(2)
j = j + 1
Loop
With Range(r(i), c)
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
i = i + j
Next i

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
=====================================

 

[Don Guillett]

Hi

Below is my requirement

I have cells in a excel in the below format

AAA 12323
AAA 56565
AAA 77789
BBB 12
BBB 13
DDD 142
CCC 121
CCC 13


I need to merge the cell of column A until the value is same . My output
should be


12323
AAA 56565
77789

12
BBB 13

DDD 142

CCC 121
13

Please let me know if this can be achieved through some macros or
forula

Thanks
Vasanth

Another
Option Explicit
Sub rowstocolumnsSAS()
Dim i As Long
Dim dc As Long
Dim sc As Long

On Error Resume Next
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i - 1, 1) = Cells(i, 1) Then
dc = Cells(i - 1, Columns.Count).End(xlToLeft).Column + 1
sc = Cells(i, Columns.Count).End(xlToLeft).Column
Cells(i, 2).Resize(, sc).Copy Cells(i - 1, dc)
Rows(i).Delete
End If
Next i
End Sub

 

[Vasanth]

Thanks ... it worked

;1600985']On Thu, 19 Apr 2012 10:51:34 +0000, Vasant

Hi

Below is my requirement

I have cells in a excel in the below format

AAA 12323
AAA 56565
AAA 77789
BBB 12
BBB 13
DDD 142
CCC 121
CCC 13


I need to merge the cell of column A until the value is same . M output
should be


12323
AAA 56565
77789

12
BBB 13

DDD 142

CCC 121
13

Please let me know if this can be achieved through some macros or
forula

Thanks
Vasanth-

This Macro assumes your cells to be merged are in column A, starting i
A1. If they start someplace else, change the initial
Set r = ...
statement to reflect the proper column.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select th
macro by name, and <RUN>.

=============================================
Option Explicit
Sub MergeSame()
Dim r As Range, c As Range
Dim i As Long, j As Long
Set r = Range("A1", Cells(Rows.Count, "A").End(xlUp))

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For i = 1 To r.Count
Set c = r(i)
j = 0
Do Until c <> c.Offset(rowoffset:=1)
Set c = c(2)
j = j + 1
Loop
With Range(r(i), c)
.Merge
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
i = i + j
Next i

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
====================================

 

[Ron Rosenfeld]

Thanks ... it worked

Glad to help. Thanks for the feedback.