+ Reply to Thread
Results 1 to 11 of 11

combine duplicate rows based on a column

  1. #1
    Registered User
    Join Date
    12-02-2015
    Location
    Texas, USA
    MS-Off Ver
    2010
    Posts
    12

    combine duplicate rows based on a column

    There are 64 columns - I would like to combine multiple rows into a single row based on Employee's name in column A. There will be dates in certain columns (if possible, only the most current date will stay, older dates will be deleted)
    Also, there will be blank cell in various rows and duplicate values in different rows of the same column, if possible, I would like to combine cell values/text into one row from the same column that has the same employees.

    This list will continue to grow and does not have a 'stopping' point other than me removing data myself.

    What I have:
    Employee's Name Date Value A Value B Value C Value D Value E Date Value G
    Employee 1 9/1/17 X X X 4/12/16 X
    Employee 2 5/3/17
    Employee 1 9/17/17 X X 3/1/16 X
    Employee 1 12/1/17 X X X
    Employee 3 2/3/16 X X 8/2/17
    Employee 2 2/14/17 X X X X X 5/5/17 X
    Employee 2 2/14/17
    Employee 4 6/7/17 X X 6/7/17 X
    Employee 3 1/1/16 X X 6/30/17
    Employee 4 X
    Employee 5 7/24/17
    Employee 6 11/1/17

    Desire results:
    Employee's Name Date Value A Value B Value C Value D Value E Date Value G
    Employee 1 12/1/17 X X X X X 4/12/16 X
    Employee 2 5/3/17 X X X X X 8/2/17 X
    Employee 3 2/3/16 X X 6/30/17 X
    Employee 4 6/7/17 X X X 6/7/17 X
    Employee 5 7/24/17
    Employee 6 11/1/17

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: combine duplicate rows based on a column

    Attaching a sample workbook enables others to work on your problem:

    To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and click "manage attachments" to open the upload window.


    To add a file to a post

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: combine duplicate rows based on a column

    Hi tho,

    I think you should rethink this problem. I believe a Pivot Table using Names and Dates as the rows is a much better format for your answer. I don't see how you can have two different dates on the same row as your problem suggests (in your answer). I can show you what I mean if you attach a sample workbook.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    12-02-2015
    Location
    Texas, USA
    MS-Off Ver
    2010
    Posts
    12

    Re: combine duplicate rows based on a column

    Thank you so much for replying to my question!!
    Also for the instructions on how to add an attachment.

    On the desire tab, I placed a quick note trying to explain the situation. Please let me know if it was unclear or further explanation is needed. EXCEL FORUM Rocks!!
    Attached Files Attached Files

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: combine duplicate rows based on a column

    Hi tho,

    This is not what I was hoping to see. What is the big question here? Let's make this problem simpler...

    See if the added sheet gives you anything close to what you need. I did an UnPivot on your Current data and then Pivoted it using Max and format as date. Does the attached help at all?

    Unpivot Lable Filter of Date Max Values Format as Date.xlsx

  6. #6
    Registered User
    Join Date
    12-02-2015
    Location
    Texas, USA
    MS-Off Ver
    2010
    Posts
    12

    Re: combine duplicate rows based on a column

    Thank you MarvinP - The attachment does help. I think we may have an issue here as I don't have PowerQuery. Is there another way to accomplished the same results in your Max Value pivot?

    What I'm trying to accomplish is consolidated data for each employee in one row across all columns (which the MAX pivot you provided seems to do what I'm trying to explain).

    I'm having a hard time getting to the end result through several methods. Find GO TO Special - blanks will not just remove the blank cells for each employee and shift everything up. Also when using a formula such as IndexMatch or vlookup, it does not account for cells with values below which are below the first row of that it find the lookup range.

    We are currently working on Excel 2013. If I'm not mistaken, Power Query is only available on Excel 2016, right?

    Again thanks again MarvinP for taking time out and helping me with this problem

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: combine duplicate rows based on a column

    Hey,

    Power Query is included in 2016 but they call it "Get & Transform". You can get the Power Query Add-In from Microsoft for free at:
    https://www.microsoft.com/en-us/down...c-197c441fa84f

    They may have made it restricted to USA only but I still think you can install it in your 2013 version. I don't think it works on the Mac so that would on a PC only.

  8. #8
    Registered User
    Join Date
    12-02-2015
    Location
    Texas, USA
    MS-Off Ver
    2010
    Posts
    12

    Re: combine duplicate rows based on a column

    Unfortunately, my work laptop has specific add ins already set up for us to choice from. I could not find the Power Query add in

    Sometimes the employee will only select Yes but not enter the date. OR put the date but not select Yes. Is there a way to add a calculated field to show if either the Yes column or the Date column is filled out to return a "yes"?

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: combine duplicate rows based on a column

    I was thinking that if you replace each "Yes" with the number 1 and blanks with Zero, you might be able to include those columns in the pivot. I didn't understand the question well enough to go further with this idea.

  10. #10
    Registered User
    Join Date
    12-02-2015
    Location
    Texas, USA
    MS-Off Ver
    2010
    Posts
    12

    Re: combine duplicate rows based on a column

    This information is tied to a data connection to Sharepoint. When the data is refreshed, it any formula I place in the cell, will get erased.

  11. #11
    Registered User
    Join Date
    12-02-2015
    Location
    Texas, USA
    MS-Off Ver
    2010
    Posts
    12

+ 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. [SOLVED] Macro to combine duplicate Rows Based on a particular Column to separate INV Headings
    By prkhan56 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-28-2017, 06:38 AM
  2. Delete Duplicate rows with duplicate in 1 column based on value from another column
    By CraigR&M in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-30-2016, 04:36 PM
  3. Delete Duplicate rows with duplicate in 1 column based on value from another column
    By CraigR&M in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-30-2016, 02:06 PM
  4. Combine duplicate rows into one row with multiple column
    By hitomi in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 09-24-2015, 06:12 AM
  5. Replies: 3
    Last Post: 11-08-2012, 09:29 PM
  6. Combine and Sum data in multiple rows if duplicate exists in 1 column
    By bnasty in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-31-2012, 07:11 PM
  7. [SOLVED] How to combine rows based on value in column A?
    By ClassicVAG in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-03-2012, 06:07 AM

Tags for this Thread

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