+ Reply to Thread
Results 1 to 6 of 6

convert multiple employee records to one record per employee

  1. #1
    Registered User
    Join Date
    05-12-2009
    Location
    pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    convert multiple employee records to one record per employee

    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.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: convert multiple employee records to one record per employee

    it can be done with a pivot table.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    05-12-2009
    Location
    pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: convert multiple employee records to one record per employee

    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.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: convert multiple employee records to one record per employee

    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.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: convert multiple employee records to one record per employee

    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
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-12-2009
    Location
    pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: convert multiple employee records to one record per employee

    John, that worked perfectly! Exactly what I needed. Thank you very much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Matching two employee directories, and adding the Employee #
    By sevanseriesta in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-26-2015, 01:58 PM
  2. Generate output employee data from input employee sheet
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-29-2015, 05:28 PM
  3. Sum if Employee has more than one record for period...
    By wrightyrx7 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-11-2014, 07:56 AM
  4. Employee Attendance Record
    By PurpleMe in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-23-2013, 02:15 AM
  5. [SOLVED] Calculate employee hours for employee evaluation?
    By Triesha in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-09-2006, 10:55 AM
  6. [SOLVED] Return employee name when the employee's number is entered
    By Mike in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-16-2005, 03:05 PM
  7. Add Employee / Delete Employee Form questions
    By WinterCoast in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-11-2005, 08:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1