Excel help needed in comparing 2 lists to each other

Aaron Rothberg

I think we would need more information about what other data points are available, if any, in these reports. If we go down the path of your idea with date projections into the future, we would need some guarantees about the data. For example, that no one donor will give two gifts on the same date with the same amount to different funds and also a guarantee that fund designations will never change for a single donor between pay periods.


At this point I would need access to the system where Heather gets her reports to see if we have any more data points to work with. Short of that access, I'm out of robust ideas.

Heather MacKenzie

Aaron Rothberg‍, Ryan Hyde‍, Chris Nungesser‍ - you're seeing all of what I can get from these reports.  I don't have direct access to the software, won't be given it, and this report has been set up the way it's set and that's that, unfortunately.  Too many users use the same report, and getting our own custom one created would take until the year 3,000.  Querying the data is what is leading to our data integrity problems, so that's also unhelpful.  


These are payroll deductions.  We are paid every other week, so they happen every other Thursday.  Employees easily can donate to different locations with the same amount - I am actually an example of that myself. 


I'm going to take some time this week and dig deeper into all your ideas and see what I can make work.  At this point, I'm open for ANYTHING that would make it easier at all.  Manually combing through 33K+ rows for quarter 2 has me nauseated about having to do the same thing for Q3 for even more rows of data.

Chris Nungesser

Attached is a new spreadsheet that, I think, gives a better view on what I had previously suggested (plus I added a few fields to check for issues in the data). What's below are general thoughts about the report, some details about the fields I added, and background on the employee scenario I put in the sheet.

GENERAL THOUGHTS

Thinking about how functional this solution is, I am wondering how often you and your team are updating the amounts and designations in your database? And, based on your last post, whether or not that information even comes to you? If donors are making changes super often or if that information is not getting to you, then this could be even more challenging.


Since this this strategy is based on the previous quarter's information, any changes that happened in a quarter won't match up with the previous quarter's record. If possible, I would keep track of what changes are made during any quarter - e.g. employees that change their giving, new people that begin giving, etc.


For this to work, you'll also need to save the finalized version of each quarter's report. You'll need the previous quarter's information to compare against each subsequent quarter.


INFO ON NEW/ADDED FIELDS

The sheet I've attached is similar to what I shared before. I moved all the new/added columns to the end of the report. I just felt that would be more straightforward to work with each quarter. Also, I added some fields to check for some incongruities between the two spreadsheets.


Basic fields needed for the report to work (starting with column J):

• Donates To - Displays the Prior Quarter's donation location for a gift that has the same value from the same person.
• Gift Count - Counts the number of gifts per person within the report. This is needed to create a unique ID to pair the gifts to a location.
  • with this formula, if you delete a row, you'll see an error message for the all rows below the deleted row. Just reapply the formula to column and everything look right again. If you decide to use this report, you'll see what I mean as you make edits. It's just a temperamental formula.
• Unique ID - Creates a unique number for each person based on Person ID, Value, and Gift Count.

Additional fields that can help with you spot issues in the report (these are the fields I put in blue):

• Match? - Can be used as an indicator if there's a discrepancy between last quarter's data and the new quarter's data. This checks to see:
  • if the current quarter's gift amount matches the gift amount from the previous quarter
  • if the count of gifts per pay period for a person matches the same count as last quarter (the Count is in column O).
• 2nd Unique ID is a value based on the Person ID and the Process Date.
• Gifts per Pay Period - counts the number of different designations for each person for each pay period.

You'll notice that the new/added columns in the previous quarter's tab (called Q1 here) do not have any formulas. And new/added columns in the current quarter's tab (called Q2 here) do have formulas. As I noted in a previous post, once you've locked in your data for a quarter, I would recommend doing a Copy and Paste where you Paste the Values of the cells back over the formulas. This prevents anything from changing on you as you or others continue working with the sheet. So I did the same here in the Q1 tab just to replicate how I think it'd look as you work with the two spreadsheets.

EXAMPLE SHEET

The scenario in the spreadsheet I've attached has six different employees. The gifts shown are for two pay periods from each quarter (just to show that the formulas work over multiple pay periods). And just like before the sheets are sorted by Person ID, then Process Date, and then Value.

• Aaron R - has three designations. They're all different amounts.
• Ryan H - has three designation. Two are $10 and one is at $15.
• Chris N - has two designations. But let's say that Chris changed the locations for his gift for the second quarter. So it's the same number of designations and same amounts, but the locations changed from Q1 to Q2. This won't reflect in the report. It's going to pull from the previous quarter's info. There won't be an error message. Q2 doesn't appear to have any issues just looking at the spreadsheet. You'd just have to keep track of these changes and manually adjust the report. 
• Heather - Increased her giving. She was giving $20 each week to St. Helena. But now she's adding a second location for $10. That flashes a couple of error signals that the two quarters aren't matching (the hyphen in Donates to and that the Values don't match). You'd get a similar error if Heather had simply increased her giving amount, but kept the locations the same. This helps provide an alert that you may want to review this particular donor.
• Karen - went from two locations down to one, but the amounts stayed the same. The Location formula found a partner, but the "Match?" field flagged that there's an issue here. But like the Chris scenario, it'd just be best to track these situations throughout the quarter so you can be on the look-out for them as your process your report.
• Johnny is a new hire and didn't appear in the prior quarter, but is there now. So there's an error because there wasn't a match for him previously. 

I feel like this is about as close as I can get based on the information available. Anything closer is just creating more formulas to try to spot errors. I had some additional data check formulas, but they caused Excel to take longer to process. Considering that, I didn't feel that those fields added enough value to the report, so I removed them.


I had thought about a second Match type column that would display an error message across a person's entire record if any single row triggered a Does Not Match alert. This way you could filter the report by the "Does Not Match" people (and see their entire giving record) and then by individual IDs. It might be easier to work with the report that way. But wasn't sure if that would be helpful or not since I don't know how you prefer to work with the report.


Hope seeing it this way helps. Let me know your thoughts.


Chris

James Andrews

I know this is a completely unhelpful post on this thread but I'm gonna post it anyway:


I'm still just blown away that the donation report they're giving you DOESN'T TELL YOU WHAT THEY'RE DONATING TO.


30K+ gifts that you have to comb through and try to figure out by hand? That is insanity! No offense to your organization, but it's legitimately one of the most shortsighted things I've ever heard. "Oh here's your report, get all this into RE and make it accurate, but also we're not going to provide you with this one crucial piece of information that would make our GL reconcile with fundraising, good luck and no more information will be forthcoming."  


OK carry on, sorry.

Ryan Hyde

James Andrews‍, I'm glad you said that, because that's honestly all I can think about. It's both shortsighted and disrespectful to Heather and, frankly, the whole organization. It's mind blowing. 

James Andrews

I mean I'm only saying it in part because I want to be able to help but I keep running up against the wall that the information just isn't there.


I mean, I'm not bragging when I say I can do LOTS of stuff in Excel—I know tons about functions, I know some VBA, I know Power Query/Get and Transform and data models and tables and pulling data directly from a SQL server and all those kinds of things. 


But all the technical knowledge in the world isn't going to generate data that isn't there, unless the data is so consistent and unique that a pattern can be established—which it's clear that it's not.


All that said, here's where my mind is going now: what I would probably do is use Get and Transform Data. I'm light on the details because I don't have time at the moment to really test it, but I'd set up a table with all the payroll dates for the whole FY as the column headers. Populate the first column with all the employees names and use an INDEX/MATCH combined with TEXTJOIN to populate each payroll date for each person with a list of their donations and the locations. Then.... something else. I'm not sure what.

Marie Stark

I know I'm late to this discussion. We have a similar situation in that our payroll department does not track  what funds the employee is giving to

I have created an access database to track all the recurring gifts, and I do exports from there every pay period.

It was a lot of work initially but now it's worth its weight in gold.

Chris Nungesser

• I like Marie Stark‍'s suggestion. Putting in the time to input all the records will be a pain, but it would be easier to maintain after that.
  
   
• I am attaching another example sheet. This is similar to what I posted before in terms of process. The difference here, building on Marie's idea, is that, instead of trying to match up the current quarter's gift information to a previous quarter's record, this sheet is linking the current quarter's gift information to a main record. Heather, that main record could be what you export out of your Access database. I'd think you'd still want to compare the two records as a double-check to make sure the quarterly gift record and the Access database are in agreement.
  
  
  One other change for this version, gifts on the "Main" tab only need to be listed once. They don't need to repeat for each instance the gift appears in the quarter's record. So depending on how you set up the Access database, you could go a few different ways with the unique ID fields and gift matching formula.
  • If you can't go the Access route, you could build a main record in Excel and make all updates to that single file. It's not the ideal solution for a number of reasons, but it might be an acceptable alternative.
    
     
• Aside from the issues I've already noted, will someone let me know if there's a problem with what I suggested? I'm wondering if there's something I'm missing. I'm seeing Ryan, Aaron, and James' posts, but feel like I addressed the issues being raised in my Excel spreadsheet (e.g. - matching multiple gifts of the same dollar amount by the same person in the same pay period). I'm just curious if there's some flaw with what I'm sharing that I'm just not seeing.
  
  
  Update: I hope this last point didn't come across as super rude. I'm just genuinely curious. Working on this has been like a little puzzle and I've enjoyed trying to figure out a solution. I also know that my posts have been long - like to an inappropriate, borderline offensive level - and there's a solid chance that nobody read them (which would be totally reasonable because no one has time to read all that. ? I mean what I wrote was more akin to some kook's manifesto than a helpful post on a professional message board). So anyway. I apologize and I thank you for your feedback.

James Andrews

I'll take a look!  Sorry, my day got busy yesterday and I only had time to complain rather than offer any real solution or look at yours! haha


EDITED TO ADD: this does look really cool, and I think it's about as good as you can get, considering the lack of information -- it identifies what matches and points out the ones that don't for further research.  Nice!

Heather MacKenzie

James Andrews‍ to address your complaint (because I SHARE THIS EXTREME FRUSTRATION WITH YOU!!!), the people that built our current payroll system had no idea what Philanthropy needed and didn't involve us.  To get our corporate office to even allow us to have payroll deduction still was a struggle and we're still scared of losing it.  This report format that I shared with you all is the best I have for right now.  The report that we get after each payday separates itself out by donation location, but is full of glitches, false duplicates and other wrong information.  So it's not trustworthy.  This report is accurate, but can only be run by what payroll each person sits on.  AND NEITHER ONE OF THEM SHOWS US WHAT FUND!!!  Or even CAN show us what fund...


However, in trying to work with what I have, I'm so thankful that you all have been working on this puzzle with me and trying to help make it more palatable and feasible to get real accurate data out to each of our offices so that we can accurately record our donors and how much they've contributed!  


I have yet to be able to take the time to do more than read through all your thoughts, and I'm sorry for that.  I will come back with any feedback, thoughts, and/or questions once I've started digging into it all.

Ryan Hyde‍ Chris Nungesser‍ Marie Stark‍ Aaron Rothberg‍ 

James Andrews

How frustrating! Take a look at Chris's solution, I think it might be your best bet.

Heather MacKenzie

Well, guess what.....  I don't even have Access on my computer.  Sigh.