Year End Tax Report

Hi Miki,

We have a custom report that helps with some of the things discussed in this thread. It's available in the Shared Reports workspace - Shared Report #364.

Good luck!

Hi Louis, 

Would you mind sharing your lookupconcat UDF formula? I know that it will be very specific to your spreadsheet, but I'd love to see if I can figure out how to adapt it to my data.

I'm trying to do exactly what you describe: in a spreadsheet which lists all gifts on separate rows, I'd like to use an Excel formula to get all gift fields for a constituent into a single row. I'd classify my Excel skills as advanced beginner, but so far I've been unable to accomplish it. 

Thanks, 

Sarah

Sarah M. Marrs
Senior Manager of Individual Giving
Children's Theatre Company 

Do you know how to access the VBA developer area in your workbook? (It's simple once you've done it a couple of times: https://www.vertex42.com/blog/excel-formulas/custom-user-defined-functions.html)

Once you're there, insert the code below as a module. This creates a User Defined Function (basically, a custom function) that you can use in your spreadsheet.

Function LookUpConcat(ByVal SearchString As String, _
                      SearchRange As Range, _
                      ReturnRange As Range, _
                      Optional Delimiter As String = " ", _
                      Optional MatchWhole As Boolean = True, _
                      Optional UniqueOnly As Boolean = False, _
                      Optional MatchCase As Boolean = False)
                   
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String
  
  If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
     (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
    LookUpConcat = CVErr(xlErrRef)
  Else
    If Not MatchCase Then SearchString = UCase(SearchString)
    For X = 1 To SearchRange.Count
      If MatchCase Then
        CellVal = SearchRange(X).Value
      Else
        CellVal = UCase(SearchRange(X).Value)
      End If
      ReturnVal = ReturnRange(X).Value
      If MatchWhole And CellVal = SearchString Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
                ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & _
                ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      End If
Continue:
    Next
    
    LookUpConcat = Mid(Result, Len(Delimiter) + 1)
  End If
  
End Function

The formula in our spreadsheet looks like this:

=LookUpConcat($A3,$A$2:$A$470,$P$2:$P$470,CHAR(10))

$A3 is where the customer_id value is
$A$2:$A$470 is the column with all the customer_ids
$P$2:$P$470 is the column with the texts we want to concatenate IF the customer_id on that row matches $A3
CHAR(10) is the separator between the different texts (10 is the ASCII code for line break)

This is a bit convoluted but in absence of a database administrator it gets the job done!

The result is a bunch of cells containing something like this, which we merge into a Word doc:

$100.00 received on 06/19/2017 designated to Special Gifts
$85.00 received on 07/27/2017 designated to Membership Gifts

I know this is an older thread, but I wanted to share that our consortium's report for an Annual Giving Summary (per discussion here slightly more recently: community.tessituranetwork.com/.../year-end-contribution-summaries-for-donors) has been sent to Tessitura to be added to the shared reports repository.

Thank you,

Brian