You can use this macro against a copy of your worksheet--it destroys the
original while creating the new format.
Option Explicit
Sub testme()
Dim wks As Worksheet
Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
Set wks = Worksheets("Sheet1")
With wks
With .Range("a:b")
.Cells.Sort key1:=.Columns(1), order1:=xlAscending, _
header:=xlYes
End With
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
.Cells(iRow - 1, "b").Value _
= .Cells(iRow - 1, "B").Value _
& ", " & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With
End Sub
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
ah wrote:
Hi;
I need someone's help urgently.
I've few columns of data as follows in my spreadsheet currently:
Employee Number Plan Enrolled
-------------------- ----------------
12345678 Benefit Plan
09876543 Benefit Plan
12345678 Social Security Plan
Currently, the data is being displayed in 2 rows as above. Please advice
whether I could make it to display as follows in stead:
Employee Number Plan Enrolled
-------------------- ----------------
12345678 Benefit Plan, Social Security Plan
09876543 Benefit Plan
--
Dave Peterson