+ Reply to Thread
Results 1 to 10 of 10

Merge two rows into one

  1. #1
    Registered User
    Join Date
    05-07-2020
    Location
    HK
    MS-Off Ver
    Microsoft 2016
    Posts
    4

    Merge two rows into one

    Hi all,

    How do I merge two rows in which the information in all columns except one is the same? Please refer to attachment. You can see in Column A to Column C that the information is the same for each player, but in Column D the points is different for each player. If I remove duplicates then I will essentially erase the second (and third, and fourth) entry of the player. Is there a way to merge and add-up the points column of each player?

    Say for example, in the spreadsheet there are three rows of Kyle (Rows 2 to 4), but the amount of points he scored (column D) for each row is different. Is there a formula that can help me add the points column under each row together and for duplicates of all other players as well?

    Of course I could do this manually but if the spreadsheet is 1000 players long then it will be super impractical. So is there a formula to achieve the effect mentioned above?
    Attached Files Attached Files
    Last edited by png57; 05-17-2020 at 03:47 AM.

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

    Re: Merge two rows into one

    with a pivot table.

    see the attached file.
    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-07-2020
    Location
    HK
    MS-Off Ver
    Microsoft 2016
    Posts
    4

    Re: Merge two rows into one

    Thanks but how do I keep the same format as the original table? Would like to maintain original format of: Player for Column A, Team for Column B, Color for Column C, Points in Column D.

    I tried adjusting the pivot table but could not make Team for column B and Color for column C.
    Last edited by AliGW; 05-17-2020 at 03:54 AM. Reason: Please don't quote unnecessarily!

  4. #4
    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
    39,641

    Re: Merge two rows into one

    Try PowerQuery (Get & Transform). Load the table and use Group By on the three columns and sum on the fourth:

    Excel 2016 (Windows) 32 bit
    F
    G
    H
    I
    1
    Player Team Color Points Total
    2
    Kyle Gummy Bears White
    9
    3
    Auston Tropics Orange
    13
    4
    John Gothics Black
    4
    5
    Nick Bluedogs Blue
    12
    6
    Tim Vixens Yellow
    26
    7
    Mark Bulls Grey
    3
    Sheet: Sheet1
    Attached Files Attached Files
    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.

  5. #5
    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
    39,641

    Re: Merge two rows into one

    Let me know if you have any questions now that you've looked at my suggestion.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  6. #6
    Registered User
    Join Date
    05-07-2020
    Location
    HK
    MS-Off Ver
    Microsoft 2016
    Posts
    4

    Re: Merge two rows into one

    I've tried grouping it but wasn't successful in doing so. Also using PowerQuery is not an option as I will be using a company desktop.

    Would still appreciate feedback on how I can keep my original column A,B,C,D even when using the PivotTable shared by oeldere. Many thanks.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    17,741

    Re: Merge two rows into one

    Please Login or Register  to view this content.
    This doesn't make sense unless you are using an Excel version older than version 2010. Your profile says 2016.

  8. #8
    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
    39,641

    Re: Merge two rows into one

    Are you saying that your company is running an old version of Excel? PowerQuery is built in to version 2013 and above, so if they have a recent version, there isn't a problem.

    If you failed to group the data properly, then you made a mistake: I have shown in my attached workbook how it is done.

    However, as you have rejected the idea (and not even said "thank you"), I shall leave you to find an alternative option.

  9. #9
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    514

    Re: Merge two rows into one

    In a pivot table design, you can drag and move the items, adjust the table design and turn off the subtotals.
    Attached Files Attached Files

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

    Re: Merge two rows into one

    F2 =A2&B2&C2 and drag below

    E2 =IF(Countif($F$2:$F2,$F2)=1,Sumif($F$2:$F$15,F2;$D$2:$D$15),"") and drag below

    After that filter on column D and deselect empty.

    See the attached file.

+ 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] Merge multiple columns and rows as individual rows....
    By mainemojo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-05-2019, 03:43 PM
  2. How To Merge Rows but create seperate Rows for Data
    By yurbuddychris in forum Excel General
    Replies: 7
    Last Post: 08-06-2018, 08:10 AM
  3. [SOLVED] Merge multiple rows separated by blank rows
    By cgman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-20-2015, 09:03 PM
  4. Merge Duplicate Rows unique values into single rows for an infinite amount of columns/rows
    By aimeecrystalaid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-21-2013, 08:43 PM
  5. [SOLVED] Merge multiple duplicate rows into columns - more than 65536 rows
    By a3des in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-05-2013, 09:18 AM
  6. Replies: 1
    Last Post: 06-14-2012, 09:27 AM
  7. [SOLVED] Merge two rows into one, then merge into mailer?
    By tavat in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-24-2006, 02:55 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