converting column of text to text box for repetitive records

[Clark Mobley]

Hi,
I have a spreadsheet which contains employee information including job
skills. My problem is that each skill (cook,candlemaker, etc.)creates a new
row for that employee, duplicating fields that don't change, name, ss#,etc
with the only new info in the row being the skill
Like this:
Jane doe,123456789,555-555-5555,cook
Jane doe,123456789,555-555-5555,candlemaker
What I want is 1 row for each employee with the last column containing all
the skills in a text box or separated by commas. Is there an easy way to do
this in Excel? Or even Access?
Thanks, Clark

 

[Clark Mobley]

To clarify, I want to convert all skills listed in the skill column for each
employee to one cell in that row:
|Jane doe |123456789 | 555-555-5555 | candlemaker, cook |

 

[Dave Peterson]

One way is with a little macro:

Option Explicit
Sub testme01()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'headers??
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then
.Cells(iRow - 1, "D").Value _
= .Cells(iRow - 1, "D").Value & "," _
& .Cells(iRow, "D").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

But run this against a copy of your worksheet--it destroys the duplicated rows
when it copies the info to the previous row.

And it also assumes that your data is grouped by SSN (a nice unique key). If
your data isn't sorted/grouped, sort it first.

And I assumed you had headers in row 1. Either change the firstrow variable or
insert a header row.