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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.762 / Virus Database: 510 - Release Date: 9/13/04

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 |
"Clark Mobley" <(E-Mail Removed)> wrote in message
news:kVG1d.131382$(E-Mail Removed)...
> 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
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.762 / Virus Database: 510 - Release Date: 9/13/04
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.762 / Virus Database: 510 - Release Date: 9/13/04

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.




Clark Mobley wrote:
>
> 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
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.762 / Virus Database: 510 - Release Date: 9/13/04

--

Dave Peterson
(E-Mail Removed)