+ Reply to Thread
Results 1 to 12 of 12

Find duplicates and merge adjacent data for number of that duplicate into one cell

  1. #1
    Registered User
    Join Date
    09-14-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    11

    Find duplicates and merge adjacent data for number of that duplicate into one cell

    Hello,

    I hope you all had a nice Easter Break! I have a bit of an Excel hairy one which I am hoping you maybe able to help with.

    I pull data out of our system with just 2 columns Position ID and Position Title.

    The Position Title is repeated several times for each position ID linked to it i.e there are 4 position id's linked to the position title Accounts Payable Officer.

    With over 4500 position ID's I need a help on a code/macro that will loop through the position titles find the rows that contain duplicates and place all the position ID's of duplicates in the one cell (separated with Alt Enter) next to the first entry and then delete the other duplicates.

    I currently have a manual concatenate (in column c) but we can't do this manually every month, it would take me a day to do the whole thing.


    Any takers?

    Thanks in advance for your consideration!

    Sal.

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find duplicates and merge adjacent data for number of that duplicate into one cel

    Just so we're not hoping, create a workbook with BEFORE/AFTER examples of about 25 rows showing all the variables that would occur in your larger data set. With that, we should be able to offer a good macro to do this for you.

    Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    09-14-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Find duplicates and merge adjacent data for number of that duplicate into one cel

    Hi, please see attached workbook with Before and After sheet examples.
    Attached Files Attached Files

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find duplicates and merge adjacent data for number of that duplicate into one cel

    Give this a try:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-14-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Find duplicates and merge adjacent data for number of that duplicate into one cel

    Hi,

    Thanks for this Jerry it looks great. I get a "type Mismatch" or "Error 400" error message after the initial confirmmerge message box. It seems to have started working but stops half way through. I looked where it keeps stopping at and this is where some of the contractor numbers start which are just numbers they begin with a or a couple of letters. Could this be it and is there away around this? I also noticed that the contractor position titles had a dash in them so I deleted this and tried again to no avail. I am thinking it is likley the position ID's with letters.

  6. #6
    Registered User
    Join Date
    09-14-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Find duplicates and merge adjacent data for number of that duplicate into one cel

    Hi Jerry, just deleted the contractor ones with letters in the position ID's and it worked. Its very impressive. Anyway is there any chance you might be able to update the code to allow letters in ID?

    Thanks
    Sal.
    Last edited by jooga; 04-27-2011 at 12:57 AM.

  7. #7
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find duplicates and merge adjacent data for number of that duplicate into one cel

    I can't see anything offhand that would object to any particular IDs. You can, of course, post an updated sample sheet BEFORE/AFTER that more accurately depicts your needs if it differs so much the macro stopped working. I'll be happy to look at it again.

  8. #8
    Registered User
    Join Date
    09-14-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Find duplicates and merge adjacent data for number of that duplicate into one cel

    Hi, Please see attached amended before after sheet. I have highlighted the ones it seems to stop at. I have deleted these few samples and it works but I have nearly 5000 records and I can't go through it one by one. Also when I look at the cell format compared to others I cannot see any nocible difference. The only thing I can see is that when you have one position title with many position ID,s it looks like the order of position ID's for that job title are in sequence but I tried sorting by position title then position ID but with the same issue. I am sorry to be a pain but any help on this would be greatly appreciated. I feel like I am missing something very obvious???
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: Find duplicates and merge adjacent data for number of that duplicate into one cel

    hi, jooga, please check attachment, press "Start", this is one of the options to make it
    Attached Files Attached Files

  10. #10
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find duplicates and merge adjacent data for number of that duplicate into one cel

    First, I removed the macro from the ThisWorkbook module, it does not belong there. It goes in a standard code module (Insert > Module).

    Here's the corrected code, it appears your text values DO create a different vartype, so I added that.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    09-14-2010
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Find duplicates and merge adjacent data for number of that duplicate into one cel

    Yay JB your a legend! Thanks so much its working a treat.

  12. #12
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Find duplicates and merge adjacent data for number of that duplicate into one cel

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ 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