Duplicate Address Report in Raiser's Edge

Options
Shiri Pressman
Shiri Pressman ✭
Ancient Membership First Reply
in Blackbaud Raiser's Edge NXT®: General Discussion

Hi Everyone!

I am wondering if anyone has created an integrity report that will display all constituents that have the same address in RE. If there is a way to create this type of report and if so, how would I go about doing this.

Thank you!

 Shiri P.

Tagged:

Comments

  • Helen Wieger
    Helen Wieger ✭
    Ancient Membership Facilitator 1 Loyalty
    Shiri Pressman:

    Hi Everyone!

    I am wondering if anyone has created an integrity report that will display all constituents that have the same address in RE. If there is a way to create this type of report and if so, how would I go about doing this.

    Thank you!

     Shiri P.

    Hi Shiri,

    I recently went through my database and looking for duplicate addresses. Prior to running this, I would run the Duplicate Constituent search tool for both individuals and organizations in the Admin section of RE.

    My process for finding duplicate addresses was a bit involved, but it worked:

    1. Created an Excel export of all constituents (orgs & individuals) with their current preferred address. select export Head of Household only, since your spouses should have duplicate addresses. Include a unique identifier code (Const ID or Import ID).

    2. Sort the Excel sheet by address, then last name.

    3. Copy Excel sheet - you now have sheet 1 and sheet 2 that are identical

    4. On Sheet 1, use the Deduplicate data tool in Excel to eliminate duplicate addresses.

    5. Use the VLOOKUP function to identify which records on Sheet 2 no longer have a corresponding record on sheet 1. These records are the ones that have duplicate addresses. As Sheet 2 is sorted by address you can easily identify the one other constituent who has the duplicated address.

    I found that this was a good way to discover family members who had not previously been linked, or to find home businesses that were not properly linked to their owners. For addresses that are correct (but duplicate), I added the attribute "Do Not Mail (Household Duplicate)" or "Do Not Mail (Office Duplicate)". Other addresses, of course, were out of date.

  • Shiri Pressman
    Shiri Pressman ✭
    Ancient Membership First Reply
    Helen Wieger:
    Hi Shiri,

    I recently went through my database and looking for duplicate addresses. Prior to running this, I would run the Duplicate Constituent search tool for both individuals and organizations in the Admin section of RE.

    My process for finding duplicate addresses was a bit involved, but it worked:

    1. Created an Excel export of all constituents (orgs & individuals) with their current preferred address. select export Head of Household only, since your spouses should have duplicate addresses. Include a unique identifier code (Const ID or Import ID).

    2. Sort the Excel sheet by address, then last name.

    3. Copy Excel sheet - you now have sheet 1 and sheet 2 that are identical

    4. On Sheet 1, use the Deduplicate data tool in Excel to eliminate duplicate addresses.

    5. Use the VLOOKUP function to identify which records on Sheet 2 no longer have a corresponding record on sheet 1. These records are the ones that have duplicate addresses. As Sheet 2 is sorted by address you can easily identify the one other constituent who has the duplicated address.

    I found that this was a good way to discover family members who had not previously been linked, or to find home businesses that were not properly linked to their owners. For addresses that are correct (but duplicate), I added the attribute "Do Not Mail (Household Duplicate)" or "Do Not Mail (Office Duplicate)". Other addresses, of course, were out of date.

    Hi Helen, Thanks for the info!! I will try this out have a great day!
  • Carri Ann Cassabaum
    Carri Ann Cassabaum ✭
    Tenth Anniversary Photogenic First Reply
    Thank you Shiri for asking and Helen for a great solution!! Just looked for this same info, found that prior to 7.92 we could do it in RE via duplicate checking. So I figured I would need to export to Excel and could not figure how to save the duplicates rather than removing them which is what the Excel feature does! I will look into VLOOKUP. So glad for your help!
  • Matt Page
    Matt Page ✭
    Tenth Anniversary Facilitator 2 Photogenic
    Once you find them there is an alternative to the Constituent Query Tool which your accout manager should be able to set ou up with for free. It's called the "Nonconstituent Merge Utility" plugin. It has different strengths and weaknesses from the standard one. Amongst the strengths are that you can run it using a query so in the case such as this where you've already worked out which the dupes are it's very useful. It does require quite a high degree of similarity, but if that's not a problem and you only want to rund it with certain areas of your database then it's a good extra tool to have.



    Matt

Categories