+ Reply to Thread
Results 1 to 6 of 6

Consolidate duplicate rows based on upper/lowercase

  1. #1
    Registered User
    Join Date
    01-04-2016
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    11

    Consolidate duplicate rows based on upper/lowercase

    Hello

    I have a worksheet filled with rows of data where multiple rows may share the same heading, just in lower/uppercase. Is there a way to consolidate duplicates based on a certain column, then add their values for the rest of the columns? This is what I mean:

    Say I have this to start:

    MIUoIEm.png

    I want to consolidate all duplicates of "name" into one row, then add their metrics, so I end up with only 2 rows instead of 6:

    5Ok8AIx.png

    Can anyone point me in right direction? Thanks.
    Attached Files Attached Files
    Last edited by Windbrand; 03-14-2017 at 03:45 PM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,240

    Re: Consolidate duplicate rows based on upper/lowercase

    Firstly, run the following macro to put all the names in Proper order

    Please Login or Register  to view this content.
    Then if the names are in alpha order, you can use the sub totals functionality found on the Data tab of the ribbon.
    Alan


    Have you debugged your code?
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Banned User!
    Join Date
    10-29-2012
    Location
    Europe
    MS-Off Ver
    2013, 2016
    Posts
    318

    Re: Consolidate duplicate rows based on upper/lowercase

    Hi,

    If in G1 put Name, in H1:J1 is Metric1 to Metric3 then,
    in G2 John and G3 JANE (no matter how you write that name)
    In H2 =SUMPRODUCT(($A$2:$A$7=$G2)*(B$2:B$7)) then drag formula to right, then down.


    ....G..............H...............I...............J
    Name........Metric 1.....Metric 2.....Metric 3
    John.............7.............10..............11
    JANE.............2............10..............17
    Last edited by Indi_Ra; 03-14-2017 at 04:37 PM.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Consolidate duplicate rows based on upper/lowercase

    Enter formula to get unique name list in F2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    then enter in G2 and copy across and down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Or this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C D E F G H I
    1 Name Metric 1 Metric 2 Metric 3 Metric 1 Metric 2 Metric 3
    2 John 2 3 4 John 7 10 11
    3 john 2 2 1 Jane 2 10 17
    4 jOhn 3 5 6
    5 jAne 1 5 7
    6 jane 0 3 4
    7 JANE 1 2 6
    Last edited by AlKey; 03-14-2017 at 04:50 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Registered User
    Join Date
    01-04-2016
    Location
    Canada
    MS-Off Ver
    2007
    Posts
    11

    Re: Consolidate duplicate rows based on upper/lowercase

    Thank you for the replies.

    I used alansidman's macro as it preserves all the data and creates convenient grouped rows, worked great.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,240

    Re: Consolidate duplicate rows based on upper/lowercase

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

+ 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. Consolidate Rows of Data Based on Single Column
    By orutulsa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-17-2014, 09:03 AM
  2. [SOLVED] Change lowercase letters to upper-case
    By royalB in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-07-2014, 11:36 AM
  3. In excel how can I consolidate information in rows based on 2 critera?
    By kevin.willhoit in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-08-2013, 10:52 AM
  4. [SOLVED] Consolidate data in 2 rows into 1 row, based on column criteria
    By PeS in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-22-2012, 11:41 AM
  5. [SOLVED] Consolidate rows with duplicate entries into one row based on some conditions...
    By lifeisaspreadsheet in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 11-04-2012, 12:24 PM
  6. [SOLVED] Consolidate mutiple rows into one row based on ID
    By momozozo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-14-2012, 10:29 PM
  7. [SOLVED] convert a range of lowercase text to upper text or vice versa
    By jackdaw in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-16-2005, 05:06 PM

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