• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4702
  • Last Modified:

Custom Excel Report - church contribution statements

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.
1 Solution
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.
Use pivot table (see attachment).
Danny ChildIT ManagerCommented:
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.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

axessJoshAuthor Commented:
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?
Katie PierceCommented:
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.
axessJoshAuthor Commented:
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?
Katie PierceCommented:
Sorry, do that rule in Word.  Set up your template, then apply the rule next to each field there.  Leave Excel doc as is.
axessJoshAuthor Commented:
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.
Katie PierceCommented:
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.
axessJoshAuthor Commented:
gotcha, is there another layout option that I am not considering that would acheive what I'm going for?  Perhaps a horizontal rule, etc.?
Katie PierceCommented:
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.
axessJoshAuthor Commented:
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.
Katie PierceCommented:
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 $.
axessJoshAuthor Commented:
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...
Katie PierceCommented:
What if, using the "edit field" option, instead of just "$" before the amount field you did "January $", "February $", etc before each one?
axessJoshAuthor Commented:
I did that, but it goes back to a spacing layout issue... Boo
Katie PierceCommented:
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?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now