Results 1 to 3 of 3

Thread: Dealing with duplicates in my data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2014
    Posts
    205
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Dealing with duplicates in my data

    Hi I have a list of employees coming from my payroll program. Unfortunately the file pulls out each employee and each costing location you can work at. I'm trying to link the employee table to the sales table. But it says both sides have a many.

    What would be the best way to have a ONE TO MANY. Should I take the data each week and run VBA to remove the duplicates. Or can i write a powerpivot formula to deal with it.

    So Table1 is my employees and it lists you many times as you can work at each location (no control on getting the list any different). Table 2 is the sales table listing all your sales for each cycle.

    Thoughts?

  2. #2
    Board Regular
    Join Date
    Nov 2013
    Location
    Denver, Colorado - XFD1048576 - MORE Stuff here near the end. And what if you add even more stuff a
    Posts
    617
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dealing with duplicates in my data

    Hi shophoney - You might consider creating a VBA script that eliminates the duplicates in Table1. I can't see any reason you would want an employee listed twice in a list of employees.
    Happy Excelling ,

    goesr

    To enter code in your post use the format below. Click the link for more detail.
    [CODE] ... Your Code Here ... [/CODE]

  3. #3
    Board Regular
    Join Date
    Apr 2014
    Location
    Mitten State
    Posts
    223
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dealing with duplicates in my data

    This sounds similar to our hospitals where nurses can work at multiple locations and thus have several HR records showing their different pay rates, supervisors etc.

    Assuming you are using PowerQuery to load your data to the model, extract your HR data into a lookup table. Create a separate query, load the HR file, and eliminate duplicates (you may want to keep other columns of the data such as SSN, original hire date, etc.) Then link the employee lookup table to the HR and sales table. That way you preserve the HR multiple row data in case you want to calculate average pay across locations or something like that.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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