+ Reply to Thread
Results 1 to 8 of 8

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
    4

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,366

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

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

    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 2019
    Posts
    17,422

    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.

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

    Re: Trying to combine duplicate rows into one records

    Hi JetMc,

    Thanks for your help. I have a couple of questions regarding the following formula:

    IFERROR(INDEX(A$2:A$18,AGGREGATE(15,6,(ROW($A$2:$A$18)-ROW($A$1))/($L$2:$L$18=1),ROWS($A$1:$A1))),"")

    1) Where does the 15,6 come from?
    2) How does the / play a role in resolving the problem? This is the first time I have seen division being used to remove duplicates.
    Last edited by vlo2020; 05-27-2020 at 11:50 PM.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Trying to combine duplicate rows into one records

    In the AGGREGATE function the first two arguments are for function and options
    The 15 is the function number for SMALL and the 6 is the option for ignore errors.
    ($L$2:$L$18=1) will produce an array of Boolean values (TRUE/FALSE). Mathematically Excel will treat TRUE as 1 and FALSE as zero.
    (ROW($A$2:$A$18)-ROW($A$1)) will produce an array of rows 1 through 17
    The division is used to produce an array where row divided by 1 is that row and a row divided by zero is an error.
    Since the errors are ignored only the rows that have numerical value will be considered by the SMALL function.
    To see this at work utilize the Evaluate Formula feature in the Formulas tab.
    Let us know if you have any questions.

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

    Re: Trying to combine duplicate rows into one records

    Thanks JetMc for the clear explanation. Makes sense now

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Trying to combine duplicate rows into one records

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Combine Duplicate Rows and Combine Data in Rows
    By cherylmcgk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-08-2013, 12:04 PM
  2. Replies: 2
    Last Post: 08-12-2011, 04:13 PM
  3. Sort and combine records/rows from two sheets.
    By Tinman88 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-27-2009, 03:36 PM
  4. Identifying duplicate rows (not just records)
    By mc515 in forum Excel General
    Replies: 2
    Last Post: 05-20-2008, 11:34 AM
  5. [SOLVED] How to combine Excel 2002 files and remove duplicate records?
    By Dave542 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 04-06-2006, 08:10 AM
  6. [SOLVED] Combining duplicate records(rows)
    By Jen in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-16-2005, 11:59 AM

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