Solved

Custom Excel Report - church contribution statements

Posted on 2015-01-27
17
2,542 Views
Last Modified: 2015-02-03
I have an Excel spreadsheet with a church's donations setup for the year.  The spreadsheet is setup with each donor's name address on the row along with their giving totals for each month of the year, and total for the entire year.

I'm wondering if its possible save me a ton of cut and paste work to create a custom report that will auto-populate a report for the donor contribution record.

Here are my columns
Donor Name | Address | Jan | Feb | Mar | Apr | May | June | Jul | Aug | Sept | Oct | Nov | Dec | Year Total

I'd like it to lay out

<header> (I can do this)

<donor name>
<donor Address> (this may require an intermediate step to separate address into columns (address, city,state,zip))

Giving Records

<jan> amount
<feb> amount
<mar> amount
<apr> amount
<may> amount
<june> amount
<july> amount
<aug> amount
<sept> amount
<oct> amount
<nov> amount
<dec> amount
============
<year total>

It would be awesome if I could setup the connections on the report "template" and then just create something to make it run and produce the reports.

Any Suggestions or direction is much appreciated.  I'm not even sure what to call what I'm doing to look up in Google.

Thanks in advance.
0
Comment
Question by:axessJosh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
17 Comments
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 40574440
Excel can do that for you using a function called Transpose. Select the existing data, including column headings, select Copy, click on an empty cell where the data can be pasted, then select Paste Special. In the Paste Special dialog, check the Transpose checkbox, then click OK. Your pasted text will now be transposed so that columns become rows. Once that is done, add a Sum() function to get your totals.
0
 
LVL 7

Expert Comment

by:slubek
ID: 40574762
Use pivot table (see attachment).
Donations.xlsx
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40574914
It looks like you want Excel to actually write the letters to each of your doners.

You *could* do this, but I'd say it was more of a natural candidate for a mail merge .

It can be a bit fiddly to do this, but not horrific.  If you let us know which version of Office you have, I'm sure we can advise more precisely.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 2

Author Comment

by:axessJosh
ID: 40575884
Thanks DanCh99.

i have pushed forward with mail merge and it's going to do what I want.

there is one catch.

There will always be an <<address block>> but not every month will have data included.

I'd like it to layout the results in a table format but can't figure out how to have it skip if no data exists.  i see that functionality, but not how to use it.

For Instance, I'd like it to layout the results in columns and rows for month | total, but only create the row necessary for the cells with data present.

Any advice?
0
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40576171
In a mail merge you can select "Rules".  Put the cursor next to the January field, then click "Rules". Click "Skip Record If..." Select the field and under Comparison, select "is blank".  Do this next to each month.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 40576175
Thanks Katie, but how would I create a table for the months that do exist... is it possible to select an entire table row and apply the Rule?
0
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40576182
Sorry, do that rule in Word.  Set up your template, then apply the rule next to each field there.  Leave Excel doc as is.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 40576188
yes, that is where I am setting it up.

I'd like to create a Table in Word so the data lines up and looks nice and justified.
0
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40576200
Oh, I see.  Not sure if you could make it skip the way you want if it is in a table format.  If the months are designated to rows in a table, you could have the data next to them be blank, but I'm not sure you could have it skip it entirely and have the months "move up" so to speak, so as to avoid blank spaces.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 40576215
gotcha, is there another layout option that I am not considering that would acheive what I'm going for?  Perhaps a horizontal rule, etc.?
0
 
LVL 7

Accepted Solution

by:
Katie Pierce earned 500 total points
ID: 40576233
Here, I used slubek's sample Excel doc and added columns that name the month IF there was donation data for that month (I only did Jan-Mar, for this example).  Then I created a quick letter where I used the field I created, plus the "blank" rule, then hit Tab, so that the next field (the donation amount) lines up on where the cursor tabbed, and applied the same "blank" rule.

The donation data should line up vertically because that field is all lined up on the Tab, not by spaces, so it shouldn't be affected by the length of the Month Name (if it is, just hit Tab again on all of them).

Also, the entire line should blank out if there was no donation that month--month name and amount.
Letter.docx
Donations.xlsx
0
 
LVL 2

Author Comment

by:axessJosh
ID: 40576252
this is just about what I need.  The last piece is that I have the month titles as column headers.  Is there a way to pull that info as the description with the amount?  I also noticed that it's not pulling the data as Currency eventhough that is how its defined in Excel.
0
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40576265
Regarding the months, that's why I created columns that would name the month if there was relevant data, but be blank if there were not.  I'm not sure you can combine them.

Regarding the Currency, you can right click on the amount field in Word and click "Edit Field", then check the box on the far right that says, "Text to be inserted before:" then enter the $.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 40576301
yea, I tried adding a column with months but that doesn't work with my data layout.

the excel is setup

giver 1 | jan | Feb | Mar | etc.
giver 2 | Jan | Feb | mar | etc.

I'm trying some work-arounds with the text before option under Edit Fields, but can't put a tab there...
0
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40576365
What if, using the "edit field" option, instead of just "$" before the amount field you did "January $", "February $", etc before each one?
0
 
LVL 2

Author Comment

by:axessJosh
ID: 40576431
I did that, but it goes back to a spacing layout issue... Boo
0
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40576440
Well, you could figure out how much space you need after the longest month (probably September), then increase the spaces by the correct number of characters for each month.

So, if you had:

September  $                                (2 spaces before the $)
October       $                                (7 spaces before the $)

So as I typed it I was proven wrong--October needed 7 spaces, not the anticipated 5.  But still, you could get them close?
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question