+ Reply to Thread
Results 1 to 4 of 4

Trying to combine duplicate rows into one records

  1. #1
    Registered User
    Join Date
    05-19-2020
    Location
    Ottawa
    MS-Off Ver
    Office 2016
    Posts
    2

    Trying to combine duplicate rows into one records

    Hi everyone,

    I am new to this forum and I am have a question regarding combining duplicate rows into one record. I am having a really hard time coming up with an excel formula to perform this task. I want to avoid using a pivotable. I try to follow this example, and the results were ugly. This is the example I used to resolve my issue:


    Spreadsheet is attached. Thank you

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    37,523

    Re: Trying to combine duplicate rows into one records

    Welcome to the forum.

    This will be easy with PowerQuery, but what should be displayed in other columns?

    Please mock up the first 2-3 rows of result data as you want it to look and post the workbook again.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    05-19-2020
    Location
    Ottawa
    MS-Off Ver
    Office 2016
    Posts
    2

    Re: Trying to combine duplicate rows into one records

    Hi,

    Thanks again for your help. Refer to the spreadsheet for more details. Before using powerquery, I tried to remove the duplicates for each contractor, and retrieve the oldest start date and latest renewal date, without deleting any other records.
    What I did was sort the contractors by name, sort the start date by the oldest start date, and finally sort by latest renewal date using the sort button. When I use the delete duplicate button, I did not get the results I wanted.
    I attached another spreadsheet. Refer to the section end result on how should the spreadsheet should look like after removing the duplicates.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    9,564

    Re: Trying to combine duplicate rows into one records

    This proposal adds a helper column which may be moved and/or hidden for aesthetic purposes.
    The helper column (L) is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The names are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The columns for Active Assignment? through End Date on Vender RQQ are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The column for Last Renewal Date is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that conditional formatting is used to hide zero values in the Last Renewal Date column.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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