How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Richard Your Own Question
Richard
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 35378
Experience:  Over 15 year experience resolving Microsoft Office Issues
32989067
Type Your Microsoft Office Question Here...
Richard is online now
A new question is answered every 9 seconds

I am a fundraising consultant working on a spreadsheet that

Customer Question

I am a fundraising consultant working on a spreadsheet that will be provided to a third party to analyze our donors to determine future giving. I have pulled last names past donations and dates of donations from our database. The third-party needs to know the amount and date of first donation, amount and date of last donation, amount and date of largest donation, and total number of donations and total amount of giving over history. Here is an example of the data I have:
Carmon 125 11/25/2012
Carmon 60 12/1/2012
Carmon 500 12/4/2012
Carmon 250 2/10/2013
Carmon 75 11/7/2014
Carmon 500 11/7/2014
Carmon 150 12/28/2015
Carmon 1000 12/28/2015
Carmon 300 5/17/2016
Carmon 300 5/17/2016
Allwein 100 12/9/2015
Hinger 20 12/15/2012
Hansen 1000 6/9/2011
Kibbe 50 12/5/2012
Kibbe 50 2/5/2013
Kibbe 50 3/5/2013
Kibbe 50 4/5/2013
Kibbe 50 5/5/2013
Kibbe 50 6/5/2013
Kibbe 50 7/5/2013
Kibbe 50 8/5/2013
Kibbe 50 9/5/2013
Kibbe 50 10/5/2013
Kibbe 50 11/5/2013
Kibbe 50 12/9/2014
Corroto 500 11/26/2012
Corroto 20 12/1/2012
Corroto 500 12/1/2014
Corroto 200 12/31/2015
And this is how I am laying it out:
Last Name Gift Gift Last Gift Amount Last Gift Date Largest Gift Amount Largest Gift Date Total Gift Count Total Gift Amount
Allen $225.00 12/2/2014 $225.00 12/2/2014 2 $375.00
Is there a formula or way to do this in excel quickly without going through thousands of rows on my own.
Submitted: 7 months ago.
Category: Microsoft Office
Expert:  Richard replied 7 months ago.

Hi there and welcome

Yes there are formulas so do this

You would use a lookup for this

SO for the first date you use the vlookup wit the min date

The last date would be a vlookup with the max date

Then you would also do a vlookip but with Max Amount instead of date

Then you would do a countif in order to count how many times they have provided a donation

Then a sumif to total all the donations from each client

The only changes you need to do once you have got the formula right is to repeat it for each client as the criteria.

I can also do the sheet for you, through it will be a bit of work. I have made the offer for you, if you wish for me to do this for you, please accept it and I can do this for you.

Else let me know how you go please

Customer: replied 7 months ago.
The only thing I have figured out so far is how to find the vlookup function. That said, I have no idea how to actually use the function in my spreadsheet. For example, if in column A I have the 10 rows of "SMITH" (1 donor) and 20 rows of "ALLEN" (a second donor) and 30 rows of "BROWN" (a 3rd donor), then in column B I have all there donations over time, and in column C I have the dates of all these donations, how and where do I go about doing what you described above?Are you saying I would need to do the above with each set of names in the spreadsheet? If that's the case, it would take the same amount of time if I just manually examined the spreadsheet and handpicked the data out.
Expert:  Richard replied 7 months ago.

vlookup uses the following

=vlookup(Criteria of what to check, Where to look, Column number to return)

You would make one for each value you want to return

Then once you do this you just need to repeat it for each ID of the donor, so you repeat per each donor

Customer: replied 7 months ago.
This unfortunately hasn't been helpful. Thank you for your time, but this isn't what I anticipated by way of assistance on justanswer. Its not being productive or a good use of my time at this point having spent the past 45 minutes trying to do this while also using google searches to understand what your telling me.
Expert:  Richard replied 7 months ago.

ok... can you tell me please what it is you exactly require then? I provided the formulas you need to use, explained how they worked and even offered to do them for you.

Can you tell me what has not been helpful or what the issue is please?

Customer: replied 7 months ago.
I have a conference call at 11AM I need to get ready for, so can likely send a reply after. Thanks.
Expert:  Richard replied 7 months ago.

ok

Related Microsoft Office Questions