convert multiple employee records to one record per employee

**jmccullough** · 07-26-2016, 10:51 AM

This is what my file currently looks like. Multiple records for each employee.

Employee ID Employee First Name Employee Last Name Bargain Unit Absence Reason Initial Balance
1000244 Barb Wire ESPA 260 ILL 12
1000244 Barb Wire ESPA 260 VACATION 22
1000244 Barb Wire ESPA 260 PERSONAL 4
1000177 Linda Savage ESPA 260 ILL 12
1000177 Linda Savage ESPA 260 VACATION 11
1000177 Linda Savage ESPA 260 PERSONAL 4
1000177 Linda Savage ESPA 260 SICK BANK 0

This is what I would like it to look like. One record for each employee.

Employee ID Employee First Name Employee Last Name Bargain Unit ILL VACATION PERSONAL SICK BANK
1000244 Barb Wire ESPA 260 12 22 4
1000177 Linda Savage ESPA 260 12 11 4 0

Any help is appreciated.

**oeldere** · 07-26-2016, 10:57 AM

it can be done with a pivot table.

**jmccullough** · 07-26-2016, 11:08 AM

Thank you for the quick response however I need it in this format so that I can import the excel file into Access and link it to other files.

**oeldere** · 07-26-2016, 11:17 AM

1. It would help to see the workbook.

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**JohnTopley** · 07-26-2016, 11:44 AM

Try (assuming I have the headers right!)

in Sheet2

in A2 and copy down: Unique list of employee IDs

=IFERROR(INDEX(Sheet1!$A$3:$A$100,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet1!$A$3:$A$100),0,0),0)),"")

in B2

=VLOOKUP($A2,Sheet1!$A$2:$G$100,COLUMNS($A:B),0)

copy across to D

In E2

=IFERROR(INDEX(Sheet1!$G$2:$G$100,MATCH(1,($A2=Sheet1!$A$2:$A$100)*(E$1=Sheet1!$F$2:$F$100),0)),"")

Enter with Ctrl+Shift+Enter

Copy across to H

Copy all columns down