merging of data automatically

[ah]

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

 

[ah]

As a result of that, as long as the Employee Number repeats, I want Excel to
merge all the plan enrolled by this employee into 1 row. Please take note
that I've hundreds of employees in the spreadsheet now, thus I'm not be able
to do this manually. Can anyone please help?

 

[Dave Peterson]

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