+ Reply to Thread
Results 1 to 18 of 18

Macro to find duplicates, concatenate cells, then delete old duplicates

  1. #1
    Registered User
    Join Date
    06-06-2013
    Location
    Moldova
    MS-Off Ver
    Excel 2010
    Posts
    11

    Macro to find duplicates, concatenate cells, then delete old duplicates

    Hi everyone,

    I am very new to VBA, so I'm not even sure if what I'm trying to achieve is possible with a macro. I guess I'm looking for advice on whether it is possible, and if so, how to go about building it.

    My issue is that I have a sheet of data that contains duplicate rows. I want to first locate any duplicates, based on a name field, then merge those duplicates into one row. I'll need to concatenate the values in each duplicate's "description" field, and comma separate them. Then I need to add up the total of the duplicates' "value" fields. Then I need to delete the duplicates, and just have that one merged row remaining.

    I've attached a sample sheet for reference.

    I'm sorry to post the question before trying anything myself, but I've spent hours on these forums trying to find another example that matches my problem, and I just can't find anything that works.

    Thanks so much for any input!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Macro to find duplicates, concatenate cells, then delete old duplicates

    Hi givemepuppies, welcome to forum.
    To more understanding your question, can you output a sample answer (maybe just Philip Pearson )that you wish to get?
    Thanks
    -If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".

    -Always upload a workbook before start your question
    To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

    +++ If my answer(s) helped you, please add me reputation by click on * +++

  3. #3
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Macro to find duplicates, concatenate cells, then delete old duplicates

    Try something like:
    Please Login or Register  to view this content.
    Last edited by ragulduy; 06-06-2013 at 05:47 AM.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro to find duplicates, concatenate cells, then delete old duplicates

    See if this is what you wanted.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-06-2013
    Location
    Moldova
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Macro to find duplicates, concatenate cells, then delete old duplicates

    Oh my gosh, that is EXACTLY what I was looking for!! Thank you SO MUCH!! You have seriously just saved me tons and tons of stress. Thank you so much!

  6. #6
    Registered User
    Join Date
    06-06-2013
    Location
    Moldova
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Macro to find duplicates, concatenate cells, then delete old duplicates

    Quote Originally Posted by yudlugar View Post
    Try something like:
    Please Login or Register  to view this content.
    Thank you, that worked perfectly too! Thanks so much for your help!

  7. #7
    Registered User
    Join Date
    06-06-2013
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Macro to find duplicates, concatenate cells, then delete old duplicates

    Jindon - This solution is amazing! I have very similar task and could potentially utilize this as well if I had a better understanding of what each line was doing. Is there any way you could add comments?

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro to find duplicates, concatenate cells, then delete old duplicates

    See attached.

    Generalized and commented.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    07-17-2012
    Location
    N/A
    MS-Off Ver
    Excel 2010
    Posts
    218

    Re: Macro to find duplicates, concatenate cells, then delete old duplicates

    For the "ConcCols" array, can you code it so that it only shows unique values in the concatenated column? (should the same piece of equipment be listed twice for the same person).

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro to find duplicates, concatenate cells, then delete old duplicates

    Now you need to open a new thread for your own question.

  11. #11
    Registered User
    Join Date
    06-06-2013
    Location
    Moldova
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Macro to find duplicates, concatenate cells, then delete old duplicates

    Quote Originally Posted by lesoies View Post
    For the "ConcCols" array, can you code it so that it only shows unique values in the concatenated column? (should the same piece of equipment be listed twice for the same person).
    Actually, that's a really good point. I just re-opened this thread, because I realized yesterday that I have that problem too, with duplicate values showing up in the "description" field.

    Also, is it possible to use a macro to just merge all of the duplicate rows for the one person, into the one row? So instead of comma separating the values and totaling the price values, copy those values to new columns within the one row? I don't know if I'm making sense...

    John Doe Fire hose $45
    John Doe Alarm $45
    John Doe Monitor $45

    would become:

    John Doe Fire hose $45 Alarm $45 Monitor $45

    Would that be easier or harder than concatenating them?

    Sorry for all of the questions, I'm completely new to VBA, and trying to follow the logic. I'm used to Java, so I get the ideas behind the loops, I guess I'm just thrown off by the syntax or something...

    Thank you all so much for your help!

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro to find duplicates, concatenate cells, then delete old duplicates


  13. #13
    Registered User
    Join Date
    06-06-2013
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Macro to find duplicates, concatenate cells, then delete old duplicates

    In response to Jindon's commented version: This is awesome! Thank you so much! Was revisiting this and wondered if we can or what is the best way to throw in the concatenate that we want an ALT+ENTER to give the cell a more list like feel? I am concatenating up to 94 items - the cell gets very wide lol! I'd love to see something more like one row containing Ruth Rocci in A1 and B1 is Sensor, (ALT+ENTER so in the same cell just listed below Sensor) Keychain. Hope this makes sense:




    Ruth Rocci Sensor,
    Keychain

    Again - thank you so much for your help!

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Macro to find duplicates, concatenate cells, then delete old duplicates

    Change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    06-06-2013
    Location
    Michigan
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Macro to find duplicates, concatenate cells, then delete old duplicates

    Worked like a charm Thanks a bunch!!

  16. #16
    Registered User
    Join Date
    03-04-2016
    Location
    bangalore
    MS-Off Ver
    2007
    Posts
    2

    Re: Macro to find duplicates, concatenate cells, then delete old duplicates

    Thanks a lot Jindon, but my problem here is that I want to keep the duplicates and dont want to duplicate them and secondly the result of concatenation should show for all the duplicates
    Example:

    1 A
    2 B
    1 C
    2 D

    Result
    1 A,C
    1 A,C
    2 B,D
    2 B,D

    Could u help me over here pls?

  17. #17
    Registered User
    Join Date
    03-04-2016
    Location
    bangalore
    MS-Off Ver
    2007
    Posts
    2

    Re: Macro to find duplicates, concatenate cells, then delete old duplicates

    Thanks a lot Jindon, but my problem here is that I want to keep the duplicates and dont want to duplicate them and secondly the result of concatenation should show for all the duplicates
    Example:

    1 A
    2 B
    1 C
    2 D

    Result
    1 A,C
    1 A,C
    2 B,D
    2 B,D

    Could u help me over here pls?

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Macro to find duplicates, concatenate cells, then delete old duplicates

    macroofc welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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