+ Reply to Thread
Results 1 to 13 of 13

Comparing data in multiple rows

  1. #1
    Registered User
    Join Date
    04-20-2009
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2003
    Posts
    6

    Comparing data in multiple rows

    Hi,
    I have a spreadsheet containing donation data for a non-profit organization with over 25,000 rows. The spreadsheet contains a lot of data, and is sorted by donor name, donation date (when the donation was made) and the feedback date (feedback on how the donation was used).

    I'm looking to analyze how many transactions were made by a donor within 10 days of receiving feedback for a previous donation. The condition to check for is
    (donor name is the same) AND (donation date >= feedback data for a previous donation) AND (donation date <= feedback data for a previous donation + 10 days).

    How do I best perform this comparison? For instance, a donor may have made 20 donations, so I need to compare these donations amongst themselves and flag the ones where the donation date within 10 days of the feedback date for any other donation. I need to perform this exercise for each individual donor.

    The purpose is to assess how effective the feedbacks are, and if they're resulting in repeat donations.

    Any help will be greatly appreciated. Thanks in advance.
    Last edited by bimalaar; 04-24-2009 at 07:47 PM.

  2. #2
    Registered User
    Join Date
    04-20-2009
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Comparing data in multiple rows

    To add, my basic need is to loop through all rows containing data for the same donor, compare the donation and feedback dates and flag the donations that were made within 10 days of the feedback date for a previous donation (any previous donation made by the same donor, not just the most recent one)

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Comparing data in multiple rows

    I would suggest a post a representative sample file - with dummy data to avoid confidentiality issues.... outline also your "expected" results.

  4. #4
    Registered User
    Join Date
    04-20-2009
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Comparing data in multiple rows

    Hi,
    I've uploaded a sample file to highlight what I'm looking to do.. The attached file has 4 columns.
    • Donor ID - An unique id used to identify a donor
    • Donation Date - The date on which the donor made a donation
    • Feedback sent date - The date on which a feedback was sent to the donor on how the donation was utilized
    • Repeat Indicator - This is the results column with the expected results (in red). The column value is set to 'Yes' if the following condition is met, else the value is 'No'
      (donor name is the same) AND (donation date >= feedback data for a previous donation) AND (donation date <= feedback data for a previous donation + 10 days).

    If you look at the results column, the donations that have been made within 10 days on receiving feedback for any prior donation have been flagged. Note that we need to compare not just against the 'previous' donation, but against all prior donations for the same donor.

    I understand this will involve a large number of comparisons (when you compare each donation date against the feedback date for all prior donations for that donor). However, we only have 3-5 donations per donor on an average. Additionally, this is a one time exercise so I'm fine even if this takes a long time to execute against 25,000 rows.

    The complexity lies in the fact that we compare each row not just against the previous row, but need to loop back through multiple rows till the donor name is different, or the test condition holds true.
    Examples:
    - For row 4, donor 1, you will compare against rows 3 and 2, and stop at row 2 since the feedback date is within 10 days of the donation date for row 4.
    - For row 9, donor 3, you stop at row 8 since the feedback date is within 10 days of the donation date for row 9.
    - For row 6, donor 2, you stop at row 4 since the donor id is different. the test condition is not met in this case.

    Hope this helps.
    Attached Files Attached Files
    Last edited by bimalaar; 04-21-2009 at 06:44 PM. Reason: Typos

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Comparing data in multiple rows

    bimalaar, I might be missing something but presumably you can make the assumption that Feedback is always sent post Donation ?

    If so see the attached, sheet DOTE ...

    In short I sorted the data by Donor and Feedback date .. this makes the analysis much much simpler based on the above logic re: feedback/donation dates.

    Given the volume of data in play I feel this method will be far more efficient than leaving the data in it's present order and calculating using a complex LOOKUP approach...
    As it is a one-off exercise post-completion you should copy and paste the results column as values over the formulae and then resort as desired (the final column is in place to facilitate this process... or dispensing with this column just sort again by Donor/Donation Date)
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-20-2009
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Comparing data in multiple rows

    Hi,
    Thank you for the response and your suggestion. Your assumption related to the feedback always happening after the donation is correct.

    However, this doesnt work as expected when the donation date is within 10 days of NOT the immediate previous row, but one before that.

    As an example, consider rows 11 and 13 on the spreadsheet I've attached. The feedback for the donation in row #11 was sent on 2/1/2009. The donation date on row #13 is 2/2/2009. However, the indicator now shows No, since there is an intermediate entry on row #12, which does not meet this validation criteria.

    This is not an unlikely scenario, and tends to happen quite often where the feedback for an earlier donation is sent after the feedback for a more recent donation (this depends on a number of factors such as which non-profit organization the donation was made to, how soon they utilize the funds and send feedback etc.).

    Any suggestions?
    Attached Files Attached Files
    Last edited by bimalaar; 04-22-2009 at 03:42 PM.

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Comparing data in multiple rows

    In which case I would suggest something along the lines of:

    Create a Concatenation of Donor & Feedback Date, say

    F2: =A2&":"&C2
    copy down for all rows

    You can now in theory run a COUNTIF test

    D2: =IF(COUNTIF($F$1:$F1,">="&$A2&":"&($B2-10))-COUNTIF($F$1:$F1,">"&$A2&":"&$B2-1),"Yes","No")
    copy down as required

    It's not great, it will be slow-ish to calculate but it should work (based on your samples)

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Comparing data in multiple rows

    This might work as an alternative...you'll have to put "No" in D2 to start things off, since you know the first row isn't a repeat. Then put this formula in D3 and copy down:

    =IF(SUMPRODUCT(--($A$2:A2=A3),--(ABS(B3-$C$2:C2)<=10))=0,"No","Yes")
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  9. #9
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Comparing data in multiple rows

    hmm.... 25000 Sumproducts...

    though conceded OP did state it was a one-off and didn't mind if it took a while...

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Comparing data in multiple rows

    *cough* yeah...what he said...

  11. #11
    Registered User
    Join Date
    04-20-2009
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Comparing data in multiple rows

    Thanks, this appears to work great from what I've seen so far. Its slow for sure, but I'm not complaining .

    I'll let you know once I run some additional validations. Thank you so much for helping me out here

  12. #12
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Comparing data in multiple rows

    Your mileage may vary on this next suggestion, but as the file is pretty big, there's nothing to stop you from converting the formulas into permanent "yes/no" answers in the sections of your data where you know the answer really is permanent, where there's no chance of a "no" changing to a "yes" were more lines added...

    So, on the portions of your 25000 rows, highlight the yes/no flags, copy, then Edit > Paste Special > Values back over the top of it, and now the yes/no is permanent on those cells.

    ===============
    Other than that If your issue is resolved, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

  13. #13
    Registered User
    Join Date
    04-20-2009
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Comparing data in multiple rows

    Thanks a ton, DonketOte and JBeaucaire. This was really helpful

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1