Excel Macro Needed

This question is related to this Thread:  https://www.experts-exchange.com/questions/29072079/Excel-Not-sure-this-is-possible.html  and I have someone who is helping me with it.

 Ejgil Hedegaard, Attached are 2 files the Excel file with the examples and a PDF that is a watered down version of our receipt, but you can see the columns that need to be filled in.

Here is what I wrote in the  message to  you:
"ll attach the file I made up here, and if you can help me that would be great!   This only differs in the fact that there are a lot less records, and some are already just one line.  These are a weekly receipts, and the org that does these, does our year end letter. and would like the file for that to be like the one we did for the year end that you helped me with.  It is still run by the account number, and all the fields in this report are needed, and do duplicate some of the fields on the previous one you helped me on.  I can start this in a thread, but thought I'd start here. Please let me know if you need any other info."

The date filed should just be the date. Ex. 3/15/18  or 3/2/18  no zero's is fine.    

Oh, can the amount fields but done in "currency" format?  $300.00  $2,000.00?

I'm attaching the receipt so you can see where those different columns fit in.  Can the macro do those calculations?  

Let me know if you need anything else from me!

Thanks so much!!!!
Lisa
RecExForExExch1.pdf
WeeklyReceiptingTestforEXExchane.xlsx
lisafamfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lisafamfAuthor Commented:
Ejgil, I'm going to get an example of a receipt that has multiple gifts on it.  I have to wait for someone to send it to me.
Thanks!
Lisa
0
Ejgil HedegaardCommented:
Here is a file that transform each account to a single line, just like the previous.

I don't understand what you want to do with the receipt file.
Let Excel create it, as pdf or print?
One for each account, with a line for each fund?
If so, a template with the setup in Excel format is needed, and a description how each field are calculated.
Please explain.
Lisafamf-Single-line-list-Weekly.xlsm
0
lisafamfAuthor Commented:
I just need a file that looks like the one you set up for me.  The receipting company puts it into the PDF.  I just wanted to show you what the fields that are being populated look like.  Maybe that wasn't helpful, lol?  At first look of your report, this looks like exactly what I need, but let me check it out a little more, and talk with my boss on Monday.  Excellent job, but I won't close this out yet.
Thanks!!!!!!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

lisafamfAuthor Commented:
Hi Ejgil, I'm waiting on our receipting company to send me one more thing today with an example of the receipts we sent yesterday.  So i will be back with you shortly if I have any changes that need to be made.  Again and again, thanks sooooo much for  your expertise!!!
Lisa
0
lisafamfAuthor Commented:
Hi Ejgil, I'm waiting on our receipting company to send me one more thing today with an example of the receipts we sent yesterday.  So i will be back with you shortly if I have any changes that need to be made.  Again and again, thanks sooooo much for  your expertise!!!
Lisa
0
lisafamfAuthor Commented:
I forgot to hit send on my last comment, lol.
I have more info, but seems quite  complicated for me to explain in here.  Can we chat live sometime, or would you like me to attempt to tell you here?
I'll try and we can go back and forth lol.
This is what I'm told we need to make no manipulation of our file every week.

A. In regards to the Fund,, Received, Non-Deducible Amount, Recieved Minus Non-Decutible Fields
  1. Can you make the headers automatically sequential?  Ex. Fund1, Received1, Non-Deductible1,Received Minus Non-duductible1, and so on and then
  2. Can you make those fields go up to 40, even if the most funds that are there are any amount from 1 to 40, so even if the longest one is 10, can it still go up to 40 ?  
I'm attaching a single line file with the headers only going 4 out, but it would be great if it could just generate 40 no matter what???
If that does not make sense let me know

B. We need 3 fields at the end That calculate that particular row (can the macro make these 3 fields below as they are? or do I have to have the columns before hitting the  macro, we just don't want to have to do it every week if the Macro can generate those headers, if that is possible??? I've highlighted the text in red)  
  1. GivingTotal  - this is the total of the Non Tax deductible and Tax Deductible fields which are added together using 2 and 3 below
  2. Non Tax Deductible- is the total of all the non-deductible Amount Fields
  3. Tax Deductible is the total of all the fields that just say Received by itself

These fields fill out places on our receipt.


Thanks! I'm confused, but hope you are not, lol
ExampleWithExtraColumnsAfterMacroIs.xlsx
0
Ejgil HedegaardCommented:
I think I understand what they want.
Check that attached does it.

It is more flexible to start from scratch, so the macro clears the result sheet before transfer, so changing the headers manually is not an option (as the macro is now).
The headers are copied from the input sheet so you can change there.
The 3 extra are inserted by the macro, and the text must be changed there.
The fund number (1 to 40) is added just right to the fund header text, but if you want a space (for readability) before the number, it is easily added.
And the number 40 can also easily be changed to something else.
Lisafamf-Single-line-list-Weekly-A.xlsm
0
lisafamfAuthor Commented:
Wow, this looks great! Thank you!!  I'll do some more testing, send it off to the receipt people, and let you know if there is any adjustments.
They do have a couple of fields they carry through, but we think are unnecessary, so we shall see, lol.
You're amazing!!!!
Lisa
0
lisafamfAuthor Commented:
Hi Ejgil,
I steered your wrong, because I did actually have any Non Tax gifts in my file, the column Tax Deductible or FP in the single line file is actually
all the Received Minus Non-Deductible columns, I told you it was the Received columns. I am so sorry, but I guess that is what testing is for!!!
Thanks,
Lisa
0
Ejgil HedegaardCommented:
No problem, it is just changing the pointer.
But in the test file nothing change, since all Non-Deductible Amount values are 0.
In the file ExampleWithExtraColumnsAfterMacroIs.xlsx the columns use Deductable, but input columns use Deductible.
I have changed to Deductible.

Here is a new file.
Lisafamf-Single-line-list-Weekly-B.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lisafamfAuthor Commented:
Hi Ejgil!
We are in some talks with our mailhouse.  they  have 3 column  headers with either no or duplicate information in them, that we do not output from our end.  Is it at all possible to make that part of the macro?  Sort of like you added the 3 columns at the end?  
One of the columns is the same as the the column FM, but it repeats through all 40.  Then there is a column they call donor ID which is our country column, and they repeat it the 40 times.  Then they  have a transaction ID header that has no data, and they repeat it the 40 times.  I can send you an example tomorrow, IF you think that can be done?
Thanks for  your patience!!
Lisa
0
Ejgil HedegaardCommented:
I don't think that will be a problem.
If you can define the logic, it can be done.

I don't know how the mailhouse use the single line list, but if I should make a number of pdf files (with VBA), similar to the pdf file in the original question, I would just use the input list as base.
Sometimes re-thinking the procedure can lead to a new and easier way to do it.
0
lisafamfAuthor Commented:
The mailhouse seems to do things in a complicated way!  Their output is eventually PDF, but they all the addresses through something also.  It would be nice to just be able to send them a PDF file and have them send it out, if that is what you are saying you can do?  I only see the address thing as an issue right now, but my boss may not.  He is out of town until  next Monday, but I could talk to him about that, if that is something you wouldn't mind trying.  For right now, I'll send you the headers they want, and I'll highlight in yellow the new ones.  The one new one would do away with one of the columns at the end of the rows, not the 3 you generated but the one before that, because for some reason they want it repeated with every fund.  I'll attaché that shortly.  Thanks!
0
lisafamfAuthor Commented:
If this is possible that would be nice, but frankly we are asking them to change their template to fit what we have.  If you want to try it, that would be awesome, but we like the macro as it is right now, so save that one, lol!
If you can tell me more about setting it up for us to generate the PDF, I'd love to  hear it.
I have notes under Fund1, and then the 3 columns marked in yellow at the end of the fund.
As always, let me know if you have questions!
Thanks in advance!!!
lisa :)
InFaithTemplateFromMailhouseForEE.xlsx
0
Ejgil HedegaardCommented:
Check attached.

Making a number of pdf files are a lot different from this, so close this question, if the result is as expected.
Then open a new question if you decide to proceed with creating pdf files.

To make the pdf files, a sheet with the setup must be made.
It must have logo (picture) and other common things, like headers, and other formatting.
Then the program can fill the relevant cells with address, funds, amounts and sums for each account.
The program can in principle do it all, but it is easier to make a common setup.
Making a pdf file is almost like printing, but a name for the pdf file must be defined.
It could be the account number and name, and perhaps also the creation date, or the date in column B.
Observe that a / can not be used in a file name, so the date must be in another format.
The files could be stored in a subfolder to where the program is, it could be named with the creation date.

If you use Outlook as mail program, it is also possible to create the mail, with the pdf file attached.
I think it can be done with other mail programs also, but I have no experience with that.
The mails can be send, but the process could also stop with the mails created, to send manually.
I sometime use that, if I want to add something to the mails, or the mails has to be send by a secure connection.
Lisafamf-Single-line-list-Weekly-C.xlsm
0
lisafamfAuthor Commented:
You are truly a genius!!!
I did notice that in some of the rows in column N United States was showing up twice.  Now this is with my original data with much more date in it.  Do  you need to see it? I have to change everything to protect the innocent, lol, so it takes a little longer.  Also, I'm not sure if they need whatever the donor ID is to just be in every one of the specific columns or they are ok with if there is just a fund in that row.  They are weird.  As I said above, I'm not telling them we can do this yet, and my boss in away until Monday.  So there won't be any activity on the question until next week.  Is that ok?
Again,  you are awesome!!!
I'll see what my boss says about the PDF's, and then I'll open a new question if he is interested.
Thanks!!!
Lisa
0
Ejgil HedegaardCommented:
I don't understand what you mean.
All I have for test is United State in column N.
Do you have duplicate information as input?
0
lisafamfAuthor Commented:
with your new macro C, I t copied and pasted a much bigger set of data into the worksheet, and ran the macro.  There are some cells in the Donor ID column that have this "UnitedStatesUnitedStates".  I looked at it a bit, and what it is, for some reason our entry person, instead of combining 2 separate gifts for the same person entered them as 2 gifts....it could be that it was on 2 different dates, or the donor sent in 2 checks on the same date, so she wanted to show that it was 2 checks.  So because there are 2 gifts of the same fund, its duplicating the United States twice in the one field.  It may not be a problem for the mailhouse, I'm not sure.  I can find that out after I tell them next week that we can do what they want, lol.   So, yes, to answer your question, there was duplicate information input, lol.
0
lisafamfAuthor Commented:
Ejgil,
The mailhouse remapped their software, so I am using macro B.   Thanks so much for all your work on this.  I may come back to you and ask you to revise the year end macro you did, but I'll address that later.  We manipulated some thinks on that one, that you programmed on this one.  You are amazing!!!  
I will close this out
Thanks!!!!!
Lisa
0
lisafamfAuthor Commented:
Did this give you points?
0
Ejgil HedegaardCommented:
Yes, the question is closed now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.