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

On Thu, 19 Apr 2012 10:51:34 +0000, Vasanth wrote:


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
=====================================

On Thursday, April 19, 2012 5:51:34 AM UTC-5, Vasanth wrote:
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




--
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

Thanks ... it worked

On Fri, 20 Apr 2012 05:40:49 +0000, Vasanth wrote:

Thanks ... it worked





Glad to help. Thanks for the feedback.