De-duplication made easy in Fellowship One

So my friend Brian Vinson has come up with a great way to find all of those pesky duplicate entries in your Fellowship One database.  He’s even asked me to post his recipe here at the Sea of Geek so you could all take advantage of it.  I’ve been playing with it all morning and it’s pretty impressive.

Brush up on some of your excel skills and get ready for some geeked out de-duplication, Bevo style:

I found this tasty little treat and wanted to share it with you. You can go directly to the F1 Duplicate Match screen if you take an individual’s F1 ID and put it into this URL: https://portal.fellowshipone.com/people/householdGeneral/DuplicateFinderResults.aspx?ind_id<<InsertIDHere>>&nav=true&searchType=status&stat=103&stat_group=102&stat_sub=0. The problem is you need a way to find potential duplicates and the A1000 series don’t have the IDs.

 Here’s what I came up with this morning.

1) Run P9400 for everyone including separate columns for First Name and Last Name as well as Date of Birth, Personal Email and F1 Individuals ID

2) Create columns for the first 3 characters of First Name and first 3 characters of Last Name using the =LEFT(XXXXXX,3) Excel formula (You can easily change this to 4,5 or 6 to restrict the results later)

3) Use the =CONCATENATE(XX,XX,XX) Excel formula to create derived columns to use in your duplicate comparison

  • Email + First Name (3) + DOB
  • DOB + First Name (3) + Last Name (3)
  • DOB + Last Name (3)
  • DOB + First Name (3)

4) Use the concatenate function to create a URL that takes you right to duplicate match screen in FellowshipOne for each potential duplicate in your Excel workbook =CONCATENATE(https://portal.fellowshipone.com/people/householdGeneral/DuplicateFinderResults.aspx?ind_id=,CellForF1IndividualID,&nav=true&searchType=status&stat=103&stat_group=102&stat_sub=0)

5) Create pivot tables from the raw data

  • Email + First Name (3) + DOB
    • Value = Count of Duplicates Match URL
    • Row Labels = Email + First Name (3) + DOB
    • Filter = Personal Email for all those that are not blank
  • DOB + First Name (3) + Last Name (3)
    • Value = Count of Duplicates Match URL
    • Row Labels = DOB + First Name (3) + Last Name (3)
    • Filter = DOB for all those that are not blank
  • DOB + Last Name (3)
    • Value = Count of Duplicates Match URL
    • Row Labels = DOB + Last Name (3)
    • Filter = DOB for all those that are not blank
  • DOB + First Name (3)
    • Value = Count of Duplicates Match URL
    • Row Labels = DOB + First Name (3)
    • Filter = DOB for all those that are not blank

6) Sort your pivot tables by the Count of Duplicate Match URL (Descending)

7) Double-click on the count for the first row and Excel will take you right to those potential duplicates

8) Copy the URL

8) Log-in to Fellowship One since you have more than likely timed out by now 🙂

9) Paste the URL into the browser while you are in Fellowship One and hit enter

10) You will now be on the potential duplicate match screen for the individual from your Excel workbook

11) Start de-duplicating your Fellowship One database

Here’s what the raw file should look like after all the concatenating:

Depending on the amount of records you have, you may need to do some pre-filtering on your data before running a pivot table. Excel will only display the first 10,000 entries to filter by.

If you’ve got any questions feel free to post them below.

Advertisements

One Response to De-duplication made easy in Fellowship One

  1. jhamner says:

    I am having problems with step 4 of this process. Any thoughts on what might need to be done to make the concatenate function wrok with the duplicate match screen in F1?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: