+ Reply to Thread
Results 1 to 12 of 12

De-duplicating, Merging and Totalling Exercise

  1. #1
    Registered User
    Join Date
    10-15-2009
    Location
    Bristol
    MS-Off Ver
    2010
    Posts
    11

    Smile De-duplicating, Merging and Totalling Exercise

    Hi all,

    I'm trying to de-duplicate a list of donors that have the same first name (column R below), surname (column S) AND postcode (column AA), whilst also merging data in the duplicates' other fields (unique email address and telephone number) and totalling their donation amount in column N. An example of the list is shown below:

    Clipboard03.jpg

    Note that non-relevant columns are hidden.

    Specifically, I want to keep just the ID of the record that has had the data merged into it (with ID1 taking priority over ID2, ID2 over ID3, etc.). The RowID is for my reference so I can later view which rows have been removed as a result of the de-dupe.

    Any help would be greatly appreciated.

    Thanks

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: De-duplicating, Merging and Totalling Exercise

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  3. #3
    Registered User
    Join Date
    10-15-2009
    Location
    Bristol
    MS-Off Ver
    2010
    Posts
    11

    Re: De-duplicating, Merging and Totalling Exercise

    Thanks for the advice. A sample workbook can be found below with BEFORE and AFTER sheets. As before, the data has been desensitized.



    Cheers
    Attached Files Attached Files

  4. #4
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: De-duplicating, Merging and Totalling Exercise

    Try this:-
    Data on sheets "BEFORE".
    Results sheet "After" starting "A1"

    Please Login or Register  to view this content.
    Regards Mick
    Last edited by MickG; 02-28-2014 at 12:24 PM.

  5. #5
    Registered User
    Join Date
    10-15-2009
    Location
    Bristol
    MS-Off Ver
    2010
    Posts
    11

    Re: De-duplicating, Merging and Totalling Exercise

    Thanks Mick,

    Works perfectly.

    I did, however, forget to include the donations data in column N, which I need to total for all the duplicates as in the updated example worksheet attached. Would it be possible to possible to add this function to the code?

    Thanks
    Attached Files Attached Files

  6. #6
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: De-duplicating, Merging and Totalling Exercise

    Try this:-
    Please Login or Register  to view this content.
    Regards Mick

  7. #7
    Registered User
    Join Date
    10-15-2009
    Location
    Bristol
    MS-Off Ver
    2010
    Posts
    11

    Re: De-duplicating, Merging and Totalling Exercise

    Excellent, thanks so much, Mick

  8. #8
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: De-duplicating, Merging and Totalling Exercise

    You're welcome
    Regrds Mick

  9. #9
    Registered User
    Join Date
    10-15-2009
    Location
    Bristol
    MS-Off Ver
    2010
    Posts
    11

    Re: De-duplicating, Merging and Totalling Exercise

    Hi Mick,
    If I want to then carry out the same task but for those who duplicate by first name, surname and Address1 instead of Postcode (column T instead of AA), how would I go about amending the code above? Sorry, but I'm a complete novice when it comes to macros.
    Duncan

  10. #10
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: De-duplicating, Merging and Totalling Exercise

    The code actually uses the following columns to differentiate Duplicates.
    "FirstName SurName Address_1 City Postcode"
    Would you like to remove the "PostCode" column ??

    Rethink !!!
    I just had another look at the code, and by my mistake it does not include the "City", but does use the other 4 columns.
    This:-
    Please Login or Register  to view this content.
    Should have been this:-
    Please Login or Register  to view this content.
    Last edited by MickG; 03-03-2014 at 07:38 AM.

  11. #11
    Registered User
    Join Date
    10-15-2009
    Location
    Bristol
    MS-Off Ver
    2010
    Posts
    11

    Re: De-duplicating, Merging and Totalling Exercise

    Thanks, I thought it might be that part of the code that I would need to alter.

    I have just changed the Array part to 1,2,3 and it has worked.

    Thank you

  12. #12
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: De-duplicating, Merging and Totalling Exercise

    You're welcome

+ 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. Excel 2007 : Help with exercise !
    By juno28 in forum Excel General
    Replies: 3
    Last Post: 04-20-2012, 06:51 AM
  2. Columbia student exercise, Monthly > Quarterly data by Macro exercise
    By alepenn in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-18-2011, 12:04 AM
  3. Need help with a exercise
    By han83 in forum Excel General
    Replies: 1
    Last Post: 04-02-2008, 08:03 AM
  4. [SOLVED] A 13 period exercise
    By Steved in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  5. [SOLVED] A 13 period exercise
    By Steved in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 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