Excel help needed in comparing 2 lists to each other

Heather MacKenzie

Hi everyone,


I'm hoping someone knows how to do this in a faster way than what I'm doing.


Here's my situation:  I have 2 lists of employee donors who are giving through payroll deduction - one for quarter 2 of 2019 and one for quarter 3 of 2019.  The report is only available (at this time) with what hospital's payroll they are on.  We have cross pollination of donations, though.  Example, I am on location A's payroll, but I donate to both location A and location B.


My quarter 2 list has (because I manually verified everyone) what location or locations each person gives to.


Is it possible to have Excel do my matching so that I don't have to manually review close to 50,000 rows?  I know about VLOOKUP, but I am under the impression that this can only match 1 to 1 and wouldn't be able to separate when someone gives to 2 different locations.


Anybody have any ideas?  It takes so long to do this matching up manually, so if there's quicker way, I'm all for it.


Thanks!

Ryan Hyde

What's the data structure look like in the one you manually verified? Specifically, how are you recording their location? Is it in a single column (e.g., Location A, Location B, Location A&B), or in two columns, one for each location? 


If it's in 1 column, then VLOOKUP should do the trick. If it's in two columns, then you should join this data into one column by creating a 3rd column with a formula that looks like "=C2&", "&D2" where C and D are your location columns. That will give you a column that has values of "Location A", "Location B", and "Location A, Location B". 


Or is there something else going on here that I haven't imagined? If you want, you can email me an anonymized sample of each spreadsheet and I can see what I can do. 

Heather MacKenzie

It's in rows, not columns, unfortunately - each donation on it's own row, Ryan Hyde‍ 


Like this:  Name | Payroll Location | Donation Location | Donation Date | Donation Amount  --  then the next row has the same info with the next donation on it.


Does VLOOKUP work for this?

James Andrews

I'm having a hard time picturing what you want your end result to be.  Are you combining lists?  Are you just checking to see who gave to which location?  Are you trying to figure out who gave to each location in each quarter?


You can use SUMPRODUCT to look at multiple criteria at once.  Or COUNTIFS or SUMIFS, depending on what you're looking to return.  But I don't know what you want to end up with.

Heather MacKenzie

Oh!  Well, yeah, that would help, right?  LOL  James Andrews‍ 


The donation location information is not yet in the quarter 3 report - the payroll system can only provide one of the other in any report at this time and the one that we currently get by donation location is full or errors and false duplicates, etc..  I need it to add it so I can distribute each list of donors to each site for reconciliation processes.  Where they donate to may or may not be what payroll location they sit on, so the donation location has to be added to the Q3 report.  Manually doing this for Q2 took about 40 hours.  I'd rather not repeat...

Jonathan Angell

It sounds like something which should be doable with a couple of vlookups - would you be able to post a couple of rows of anonymised data?


Best wishes

Jon

Rachel Cavalier

So the people who only donate to the one same location all the time are pretty straightforward to fill in this information for then? It's the people who give to more than one location that could be the tricksy ones?

Regena Weddle 2

You can create a column to concatenate the name (I usually use ID) and the Payroll or whatever location on both spreadsheets.  Then change the data to values, then use those columns to do vlookup.  So your data might look like:  Mary SmithLocation1 for her first donation row and Mary SmithLocation 2 for her second.  Then on the other spreadsheet you want to compare, she may only have a row for Mary SmithLocation 2.  When you do vlookup the Location 2 will have a match but the other one will not.  

Ryan Hyde

Okay, so each row contains 1 gift, and each gift record will ideally include the location. You have location data for one report, and need it on the other... 


I'm assuming each donation includes a unique ID for the donor, yes? Let's do a thought experiment with that assumption made.


If you were to create a new spreadsheet with everything stripped out apart from donor ID and location, you could then dedupe the list by both fields which would result in a list of every unique donor/location combination. You could then either do a SUMIF or a 'highlight duplicate values' rule on the donor ID field to identify anyone who gave to both locations. Then you could create a third column that combines locations for those people, or has the single location for people who only gave to one location. Then you dedupe one more time on JUST the ID field, leaving you with a simple list where each donor has their own row, and the only other field is the location data, whether it be a single location or a combo location.


Once you've got that, you just use VLOOKUP to apply this data to the spreadsheet with missing location data.


If this won't work for your scenario, then I really really need to see your data to understand how to proceed. Share a couple doze rows of each (anonymized, of course) and it'll be a race to see who can post up a workable solution first, haha!

Aaron Rothberg

Ryan took the words right out of my mouth! I agree with Jonathan that if you would post some sample data from your report (screenshot is fine) and then mock up a fake spreadsheet that shows us what you'd like for a result, I think you'll have a few of us trying to race each other to find a solution. I know I'm ready! ;D

Christine Prete

I personally use an excel add in tool called ablebits.  It allows me to perform many tasks with ease.  Comparing spreadsheets and merging files is a breeze with this tool.  Plus all the other bonus features it offers.  You may be able to get a free trial of it.  https://www.ablebits.com/ 

James Andrews

Heather MacKenzie:

Oh!  Well, yeah, that would help, right?  LOL  James Andrews‍ 


The donation location information is not yet in the quarter 3 report - the payroll system can only provide one of the other in any report at this time and the one that we currently get by donation location is full or errors and false duplicates, etc..  I need it to add it so I can distribute each list of donors to each site for reconciliation processes.  Where they donate to may or may not be what payroll location they sit on, so the donation location has to be added to the Q3 report.  Manually doing this for Q2 took about 40 hours.  I'd rather not repeat...

So the assumption is that for Q3 the donations went to the same locations as Q2?  But there's no donation location on the Q3 report and some people give to both locations?


Excel usage isn't really the problem here -- the question is how do you know? If employee #123 gave $50 to location A and $40 to location B in Q2, do you assume they did the same distribution for Q3?  How do you know?  


Anyway, assuming that's the case, I'm going to put some thoughts below, and I'll write this as if your stuff is in a table format. because when you're dealing with large quantities of data like this it's really helpful to have table names and column names instead of cell ranges. 


I'd add two helper columns to Q3 called "Gave to A" and "Gave to B"  and in them I'd write the following (substitute A or B, obviously)

 

=COUNTIFS(Q2Report[employeeid], [employeeid], Q2Report[amount], [amount], Q2Report[DonationLocation], "A")

So that says, if the employee ID in this table matches the one in the Q2 report, and the amount matches the amount, and the donation location is A, then count it up.  Any employee who gave that amount to A last year will have a 1 in that column.  Put the same formula in the next column with B instead of A for the location.


Assuming the giving is consistent (like if this is payroll deduction or something), each employee should have a 1 in either of the columns, and that's who gave to what.



 

Ryan Hyde

Oh it's on, A-A-Ron




(not usually a GIF guy, but this was too perfect)

James Andrews

Ryan Hyde:

Oh it's on, A-A-Ron




(not usually a GIF guy, but this was too perfect)

Post reported for being INSUBORDINATE... AND CHURLISH

Aaron Rothberg

Heather! Ryan and I need those screenshots! I'm not going to get anything done today if I have to keep clicking refresh on this thread!

Heather MacKenzie

I love you guys!  Just getting here into the community for the first time this morning (I'm on the west coast.)  I'll have the example spreadsheets to you in a moment.  THANK you all so much!

Heather MacKenzie

Here they are.  

Heather MacKenzie

and to James Andrew's question of how do I know they're still giving the same dollars to the same locations?  I don't, but if something looks weird during any of the reconciliations, we do have an inside man who can verify for us on an individual basis, thankfully.  Unfortunately, it's a broken system, and while it's headed in the right direction, it's taking a veeerrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrryyyyyy long time to get to the point where it's going to be able to be called "fixed" and "working properly."

Ryan Hyde

Boy oh boy. So looking at this, I have two questions:


1.) Do you typically have a single donor being represented this many times in your actual file?

2.) Is it actually useful to have a concatenated version of the 'donates to' column? E.g., "Clearlake, Feather River, St. Helena, Ukiah" instead of just one of those values? Like, if you had one field with all of those values put together that way, would you be able to do anything with that data?

Heather MacKenzie

This is a slightly extreme example, Ryan Hyde‍ - it's usually no more than 2.  I have about 5 who are this many, but they're easy to find and handle manually since it's so few. 


The donates to column has to end up as single rows for each location.  That way, the Clearlake donations can be sent of to the Clearlake office for their reconciliation while not showing any of the other stuff for an employee.  Each of these 4 offices needs to end up with only it's own unique data.

Aaron Rothberg

I have a solution!


So, you'll need to concatenate donor Id with gift date and gift amount in both spreadsheets, and then VLOOKUP on that key you just created. This solution assumes that no one donor will give more than one donation with the same dollar amount on the same date. If that happens the solution breaks down (unless the donor always gives to the same designation, in which case my solution is clunky, but it should work).


Spreadsheet with known designations (pay attention to the formula)





Spreadsheet without designations




Eat my dust Ryan! ;D

Ryan Hyde

Oh man, faster than me AND I like your answer more.


Attached is my attempt at this. I was still leaning on combining location names, but I'm pretty sure that's not the way to go. My spreadsheet is full of directions and process notes.

Ryan Hyde

Okay, just read your direct response to me Heather MacKenzie‍, which makes me wonder about Aaron's concatenation as well. If we're dealing with spreadsheets from two different quarters, then using the gift date on the concatenation won't result in any matched values between your two sheets. Hmmm.... thinking.

Aaron Rothberg

Oh. Oops. Ryan's right. I got matches in the spreadsheet and thought it was a good solution, but if the dates in the two data sets will never match I don't think you can build a unique key adequate enough to match designations up, at least not using VLOOKUP.

James Andrews

This is a tough one, and I'm about to leave for the day.  More thoughts on Monday, but at first glance  I'm not entirely sure how you could work this - it seems like it's missing key information.  I wonder if we can calculate something based on the WEEKNUM or something...

Ryan Hyde

Yeah, I was thinking about something involving TEXT(B42,"D") to pull the day of the month, but if all donations are happening on the same day, then that won't work either. Also if they're sometimes happening on different days for the same location, that won't work.

Ryan Hyde

Well... at the very least, you could use VLOOKUP on donor records that only do one donation (or only give to one campus, at the very least). That would leave you with only the hard ones, but it'd be pretty simple to identify the folks who only give once per quarter. Just create a new column to the right of the ID column, and starting in cell B2, use the formula =COUNTIF(A:A,A2) and populate that down the row. Anything values of 1 can be done via a simple VLOOKUP. Anything with a value greater than 1 will need more manual work.

Chris Nungesser

Hi Heather MacKenzie‍, 


It would be awesome if there was a Gift ID that your system could generate that was the same on both spreadsheets. If that's something you can do, then just use the Index/Match formula below to link the two sheets.


If that isn't an option, I have something you can try.


It assumes that the reporting periods are the same for both reports. That your first report (which I called Location) doesn't start in January while your second report (which I called GiftInfo) doesn't show gifts until some time later.


It would also be easiest if you just moved the two spreadsheets into one Excel file while you pull info from one to the other (like on two separate tabs). You can delete whatever you need before sending it out to the various locations.


For this to work, you should sort both spreadsheets by Person ID then by Process Date and then by Value. This just ensures that the donations are in the same order on both lists and will be given the same Unique ID - which you'll create using a formula listed below.


You'll need to add two columns to the report 


The first new column is Gift Count. The formula is:

=IF(ROW()=2,1,IF(A2=A1,D2+1, 1))

I put this is column D. If you put it in another column, then just change the D1 to match the column it's in. And then A2=A1, that's just looking for the Person's ID.


The second new column is Unique ID. I put this in column E. This is creating a unique ID based on the Person's ID the Process Date and the Gift Count. The formula is:

=$A2&"-"&$J2&"-"&$D2


Then you'll use an Index/Match formula to connect the two sheets. Formula:

=IFERROR(INDEX(Location!F:F,MATCH($D2, Location!D:D, 0)),"-")


Change the word "Location" to whatever you call your tab (or name your tab Location). The F in the formula is assuming the "Donates to" field is in column F on the previous page. Where you see D (D2 and D:D) is looking for the Unique ID in column D, but you can adjust this as needed).


Once you have your IDs set (and before you start cutting it up to send to the various locations), I would copy the columns that have the formulas and then Paste back in the Values (like where you can do those "fancy" types of copy of paste - if you right click and then choose Paste Values, you'll get the info in the fields without the formulas). This is important so that nobody can change the Unique ID once you've locked in your results.


Let me know if you need more info or have questions about modifying or working with these formulas (so it doesn't take a long time to get each new report run set up). I have reattached your spreadsheets with my formulas in there. You'll see I created a Mickey Mouse person just to make sure it would work with multiple people.


Chris

Heather MacKenzie

Thank you, everyone!  I have to wait until this afternoon to delve into your answers and thoughts - I have event stuff to be done this morning, but I'm excited to dig into it all.

Chris Nungesser

Hi Heather MacKenzie‍, 


I was reading back through the feed and just noticed Ryan Hyde‍'s note regarding the dates being in different quarters. If it is the case that the dates won't match because you're comparing two different quarters, then what I did with your spreadsheets won't work like I described (since I used date to create the unique identifier) and the dates in the two example sheets all matched up.


Are you using the previous quarter's report because the information should be the same for the new quarter. Like last quarter, we know John donated $5 and last quarter it was to Ukiah, so the $5 I'm seeing this quarter should also be Ukiah. Is that the thinking?


If so, then you could probably use most of what I wrote. Still sort by Person ID, then Date, and then Value. For the unique identifier, I might change it to be a combo of Person ID and Value and Gift Count. Using the same columns as before this formula would look like: =A2&"-"&L2&"-"&D2


There are definitely some issues here though. This isn't really a one-to-one match up. This just assumes the person's giving levels and designations haven't changed. The two spreadsheets also really need to have the same number of gifts/gift dates in each report for all gift records to find a partner. Or if not the same number, you need more examples of the pattern repeating in the previous quarter so that at least every row in the new quarter finds a partner (not sure if that made sense - let me know if it didn't - it's Friday after 5:00, my brain is toast, and honestly I could use an adult beverage right now ?).


Two points about the Index/Match formula I wrote:

• It begins with an IFERROR function. So if the formula can't find a match, you'll see a hyphen in the report field. Like "we looked, but couldn't find it, so here's a hyphen as a consolation prize."
• And you may also see a zero in a field from time to time. A zero means that the formula found its match, but that field was blank. It's just an empty cell.

Knowing those two things can help you spot problems (using filters) if you decide to go forward with that formula.


Have a great weekend. Hope I was able to help some. Definitely post a response if you still have any issues.


Chris

Ryan Hyde

Heather MacKenzie‍, are these automatic gifts that occur on the same day of their respective months over and over?


If so, we could use the position of the month within the quarter (1, 2, or 3) to create a new "date" field that would stay consistent from quarter to quarter. To facilitate this, I'd probably just sort by date, and start populating a new column with the relative positions of the month within the quarter. We'll call that Column L for some reason. Then you create a new column M that generates a full date string using a formula like =L2&"/"&DAY(H2)&"/"&YEAR(H2), where H is the original date column for the gift. You'd still use the actual gift date for your import of course. This new string is just to help match things up quarter over quarter.

Chris Nungesser‍, Aaron Rothberg‍, what do you guys think? Would this plug into your various methods? I have to get back to work and don't have time to think this through any further.