FilemakerPro to .CSV to NeonCRM - How to Excel?
August 19, 2016 12:42 PM Subscribe
Excel experts and fundraising professionals, ho!
I work for a small non-profit and we're currently transitioning from a janky FilemakerPro database to NeonCRM. I have all the data spread across three spreadsheets and my brain is collapsing trying to figure out the best way to deal with it.
What's the best/most efficient way to compare data between three separate spreadsheets, and manipulate it into the format I need for this import?
All the necessary/boring details lie within!
I've done a search and read through several older excel questions, but nothing is quite like what I'm dealing with. I've done some poking around excel sites, but they're a bit overwhelming! Hoping the Ask human touch will help.
I belieeeeeeve the best way to handle this import is to first import constituent data, and then import donation data (which will match to the appropriate constituents and populate their profiles within Neon). But I need to get it all sorted and prepped first.
Here's what I'm working with:
Sheet A: All constituent data from FMP (names, addresses, phone numbers, contact notes, etc.). About 1300 lines/records.
Sheet B: Edited version of sheet 1 prepared by my boss. They went through and culled every line/constituent that they don't want transferred into the new system. This spreadsheet only has constituent names and donor ID numbers, so I basically have to use it as a guide to re-edit sheet 1 for import (because sheet 1 has all the additional data we need). About 660 lines/records.
Sheet C: All donation data from FMP. i.e., details about every gift ever entered into the old system. Can match to constituents using names and Donor IDs, but this list does NOT include contact info, etc.. About 3900 lines/records.
Here's what I *think* I should do:
STEP ONE: compare sheet A to sheet B and remove any names not present on sheet B, thus leaving me with a "master contact list" to import into the new database. Import said list, thus populating our database with constituents.
STEP TWO: compare the new "master contact list" to sheet C and remove any names/associated gifts that aren't on it. Import this new "master gift list" to the new database, which will match transactions to the appropriate constituent accounts created in the previous import, thus populating our database with everything we need.
ACTUAL QUESTION(S): HOW?! What's the best way to go about comparing the data between three separate spreadsheets? I'd love to be able to put them side by side, highlight the lines that needs to be deleted, and remove them in a chunk. Do I combine the lists? Some magical formula I don't know about? My Excel skills are...lacking.
I guess I could ask my boss to re-do their constituent cull on Sheet 1, maintaining all of the data, but then I still need to compare that list to Sheet 3, and I can't figure out the best way to reconcile those two. Any advice would be much appreciated (even if it's "NOPE, this is not possible, you have to do everything manually and next time push your boss to spring for the professional data consultation.")
Bonus Q for fundraising/database champs: I believe we should still import at least the financial values of the donations made by contacts we're culling so that the new database has as complete a financial history as possible. I'm thinking we create a "dummy donor" in the new system to dump in all those historic donations for which we don't need the exact details/contact details for the actual donors. Is this a crazy/bad idea?
I've done a search and read through several older excel questions, but nothing is quite like what I'm dealing with. I've done some poking around excel sites, but they're a bit overwhelming! Hoping the Ask human touch will help.
I belieeeeeeve the best way to handle this import is to first import constituent data, and then import donation data (which will match to the appropriate constituents and populate their profiles within Neon). But I need to get it all sorted and prepped first.
Here's what I'm working with:
Sheet A: All constituent data from FMP (names, addresses, phone numbers, contact notes, etc.). About 1300 lines/records.
Sheet B: Edited version of sheet 1 prepared by my boss. They went through and culled every line/constituent that they don't want transferred into the new system. This spreadsheet only has constituent names and donor ID numbers, so I basically have to use it as a guide to re-edit sheet 1 for import (because sheet 1 has all the additional data we need). About 660 lines/records.
Sheet C: All donation data from FMP. i.e., details about every gift ever entered into the old system. Can match to constituents using names and Donor IDs, but this list does NOT include contact info, etc.. About 3900 lines/records.
Here's what I *think* I should do:
STEP ONE: compare sheet A to sheet B and remove any names not present on sheet B, thus leaving me with a "master contact list" to import into the new database. Import said list, thus populating our database with constituents.
STEP TWO: compare the new "master contact list" to sheet C and remove any names/associated gifts that aren't on it. Import this new "master gift list" to the new database, which will match transactions to the appropriate constituent accounts created in the previous import, thus populating our database with everything we need.
ACTUAL QUESTION(S): HOW?! What's the best way to go about comparing the data between three separate spreadsheets? I'd love to be able to put them side by side, highlight the lines that needs to be deleted, and remove them in a chunk. Do I combine the lists? Some magical formula I don't know about? My Excel skills are...lacking.
I guess I could ask my boss to re-do their constituent cull on Sheet 1, maintaining all of the data, but then I still need to compare that list to Sheet 3, and I can't figure out the best way to reconcile those two. Any advice would be much appreciated (even if it's "NOPE, this is not possible, you have to do everything manually and next time push your boss to spring for the professional data consultation.")
Bonus Q for fundraising/database champs: I believe we should still import at least the financial values of the donations made by contacts we're culling so that the new database has as complete a financial history as possible. I'm thinking we create a "dummy donor" in the new system to dump in all those historic donations for which we don't need the exact details/contact details for the actual donors. Is this a crazy/bad idea?
I'm with Mo Nickels. Most CRM companies are used to importing existing data, I'd ask them to do it.
posted by rachelpapers at 4:14 PM on August 19, 2016
posted by rachelpapers at 4:14 PM on August 19, 2016
Best answer: First, I'd put each of the sheets into its own worksheet/tab in a single Excel file.
Instead of deleting the rows from sheet A, I would add a column to sheet A where you indicate that a donor is to be culled.
After that, you can add a similar "culled" column to sheet C and add a lookup formula in that column. The formula would use the donor ID to check the "culled" column for that donor in sheet A. After this you'll know which of the sheet C donations need to be culled also.
Since this is an automated process you can avoid mistakes from manually comparing.
After that you can filter by the culled column and delete all rows in both sheets that are marked to be culled.
As to your question about keeping the history... instead of deleting the to-be-culled rows you could filter all of the to-be-culled rows and replace their donor ID with an ID for the dummy donor. If it were me, though, and if Neon supports it, I'd be tempted to keep the culled donors/gifts and mark them with some kind of inactive status.
posted by duoshao at 4:46 PM on August 19, 2016 [1 favorite]
Instead of deleting the rows from sheet A, I would add a column to sheet A where you indicate that a donor is to be culled.
After that, you can add a similar "culled" column to sheet C and add a lookup formula in that column. The formula would use the donor ID to check the "culled" column for that donor in sheet A. After this you'll know which of the sheet C donations need to be culled also.
Since this is an automated process you can avoid mistakes from manually comparing.
After that you can filter by the culled column and delete all rows in both sheets that are marked to be culled.
As to your question about keeping the history... instead of deleting the to-be-culled rows you could filter all of the to-be-culled rows and replace their donor ID with an ID for the dummy donor. If it were me, though, and if Neon supports it, I'd be tempted to keep the culled donors/gifts and mark them with some kind of inactive status.
posted by duoshao at 4:46 PM on August 19, 2016 [1 favorite]
What you're looking for is a join in SQL.
There's definitely also a pure Excel solution... I think it's a VLOOKUP between A and B followed by a filter and then take the result of that and do another VLOOKUP with C.
If you post the names of the columns in order, it'll take about three minutes for someone to write you a python script. Or if you feel adventurous, you can get all three into SQLite and then someone can help you with the query. (I'll volunteer myself for either of those things if you me-mail me. If you're on Windows, running Python can be a big pain in the butt. It's easy on OS X.)
posted by hoyland at 4:47 PM on August 19, 2016 [2 favorites]
There's definitely also a pure Excel solution... I think it's a VLOOKUP between A and B followed by a filter and then take the result of that and do another VLOOKUP with C.
If you post the names of the columns in order, it'll take about three minutes for someone to write you a python script. Or if you feel adventurous, you can get all three into SQLite and then someone can help you with the query. (I'll volunteer myself for either of those things if you me-mail me. If you're on Windows, running Python can be a big pain in the butt. It's easy on OS X.)
posted by hoyland at 4:47 PM on August 19, 2016 [2 favorites]
Response by poster: Thank you all!
Neon would do this work for us, of course, but at a prohibitive cost (prohibitive to us, anyway, a very small nonprofit signing up for the lowers tier of their offerings). I realize this kind of stuff is often/usually worth paying for, and in the end I'm going to end up putting in a lot of hours getting it done, but this is the choice our budget allowed.
posted by adastra at 9:49 PM on August 19, 2016
Neon would do this work for us, of course, but at a prohibitive cost (prohibitive to us, anyway, a very small nonprofit signing up for the lowers tier of their offerings). I realize this kind of stuff is often/usually worth paying for, and in the end I'm going to end up putting in a lot of hours getting it done, but this is the choice our budget allowed.
posted by adastra at 9:49 PM on August 19, 2016
Does sheet A have donor ID numbers? If it does, what you want to do will be easy. If it doesn't, it will be horrible and error-prone.
posted by flabdablet at 6:22 AM on August 20, 2016
posted by flabdablet at 6:22 AM on August 20, 2016
Best answer: Assuming sheet A does indeed have donor ID numbers, the easiest way I can think of to tackle your problem is as follows:
I'm going to assume that the three sheets are already inside a single Excel document (make a new document and paste them in, if not). So the first thing is to give the sheets meaningful names: rename Sheet A as Constituents, Sheet B as Wanted, and Sheet C as Donations.
I'm going to assume that the donor ID numbers are in column A on all three sheets. If that's not the case, adjust the following formulas so that where I give Wanted!A:A, you substitute Wanted!D:D or Wanted!F:F or whatever column the donor IDs are actually in on sheet Wanted; where I give A2 you substitute C2 or G2 or whatever cell the donor ID is in for row 2 of the sheet you're entering the formula on.
Finally, I'm going to assume that row 1 on all three sheets contains column headers, so that actual data starts in row 2.
1. Insert a new, blank column B into sheet Constituents.
2. Type the column heading Matched into cell B1.
2. Enter the following formula in cell B2: =MATCH(A2,Wanted!A:A,0)
3. Fill that formula all the way down column B.
What you should now see in column B is a bunch of numbers, along with the occasional #N/A. The numbers are the row numbers from sheet Wanted where each donor ID from sheet Constituents was found, with #N/A on rows whose donor IDs were not found in Wanted.
4. Use Excel's Filter facility to show only those Constituent rows containing #N/A in column B.
5. Delete all visible rows.
6. Turn off the filter.
7. Delete column B.
You now have a clean Constituents sheet, which you can export as a CSV. Then do exactly the same steps on the Donations sheet.
posted by flabdablet at 7:15 AM on August 20, 2016
I'm going to assume that the three sheets are already inside a single Excel document (make a new document and paste them in, if not). So the first thing is to give the sheets meaningful names: rename Sheet A as Constituents, Sheet B as Wanted, and Sheet C as Donations.
I'm going to assume that the donor ID numbers are in column A on all three sheets. If that's not the case, adjust the following formulas so that where I give Wanted!A:A, you substitute Wanted!D:D or Wanted!F:F or whatever column the donor IDs are actually in on sheet Wanted; where I give A2 you substitute C2 or G2 or whatever cell the donor ID is in for row 2 of the sheet you're entering the formula on.
Finally, I'm going to assume that row 1 on all three sheets contains column headers, so that actual data starts in row 2.
1. Insert a new, blank column B into sheet Constituents.
2. Type the column heading Matched into cell B1.
2. Enter the following formula in cell B2: =MATCH(A2,Wanted!A:A,0)
3. Fill that formula all the way down column B.
What you should now see in column B is a bunch of numbers, along with the occasional #N/A. The numbers are the row numbers from sheet Wanted where each donor ID from sheet Constituents was found, with #N/A on rows whose donor IDs were not found in Wanted.
4. Use Excel's Filter facility to show only those Constituent rows containing #N/A in column B.
5. Delete all visible rows.
6. Turn off the filter.
7. Delete column B.
You now have a clean Constituents sheet, which you can export as a CSV. Then do exactly the same steps on the Donations sheet.
posted by flabdablet at 7:15 AM on August 20, 2016
Best answer: Final thought: Instead of using steps 4-6, you might find it easier to sort your sheet on Matched, which will group all the #N/A rows together, and then delete only those rows.
posted by flabdablet at 7:28 AM on August 20, 2016
posted by flabdablet at 7:28 AM on August 20, 2016
Final final thought: if you'd rather go with duoshao's suggestion and import your data with an Inactive flag for constituents and donations you want your CRM to hide, you can prepare the CSVs for that without deleting anything.
Instead of Matched in step 2 above, use Inactive for column B's header.
Instead of the formula I gave in the second step 2 (oops) above, use =ISNA(MATCH(A2,Wanted!A:A,0))
Now instead of column B being full of numbers and #N/A it will be full of FALSE and TRUE, where TRUE marks lines whose donor IDs didn't show up in the Wanted sheet and should therefore be hidden.
If your CRM can accept an Inactive column but it wants 0 and 1 instead of FALSE and TRUE respectively, you can get that using the formula =0+ISNA(MATCH(A2,Wanted!A:A,0))
A CSV file exported from the resulting sheet will contain only the calculated values, not the formulae that calculated them.
posted by flabdablet at 7:43 AM on August 20, 2016
Instead of Matched in step 2 above, use Inactive for column B's header.
Instead of the formula I gave in the second step 2 (oops) above, use =ISNA(MATCH(A2,Wanted!A:A,0))
Now instead of column B being full of numbers and #N/A it will be full of FALSE and TRUE, where TRUE marks lines whose donor IDs didn't show up in the Wanted sheet and should therefore be hidden.
If your CRM can accept an Inactive column but it wants 0 and 1 instead of FALSE and TRUE respectively, you can get that using the formula =0+ISNA(MATCH(A2,Wanted!A:A,0))
A CSV file exported from the resulting sheet will contain only the calculated values, not the formulae that calculated them.
posted by flabdablet at 7:43 AM on August 20, 2016
Response by poster: Flabdablet's process worked like a charm (or, to be more accurate, like a formula). Thanks so much for this help; I just sighed the biggest sigh of relief!
It's probably time I take an Excel course to catch myself up.
posted by adastra at 11:00 AM on August 22, 2016
It's probably time I take an Excel course to catch myself up.
posted by adastra at 11:00 AM on August 22, 2016
Just reading through the inbuilt help on its inbuilt functions in a motivated state of mind will probably do you more good than any Excel course I've ever seen offered. You won't go even close to remembering them all, but next time you need to do a thing, your brain will ping you about seeing a few things that could have been vaguely related to that in the help docs once.
Read up on pivot tables and play with a few of those, too. They're something Excel is genuinely better at than any database.
posted by flabdablet at 1:15 PM on August 22, 2016 [1 favorite]
Read up on pivot tables and play with a few of those, too. They're something Excel is genuinely better at than any database.
posted by flabdablet at 1:15 PM on August 22, 2016 [1 favorite]
« Older From 0 to "programming" in $2500 | Two Money Questions re: the Thiel/Gawker Situation Newer »
This thread is closed to new comments.
posted by Mo Nickels at 2:19 PM on August 19, 2016