Excel - Not sure this is possible?

I need to send info to a mail house for year end letters.  Unfortunately our third party database can not give us what the mail house calls a flat file.   What we already  have on one line is the Account number, name, address, all the different salutations,  the YTD including if it's non-deductible and the number of transactions.  This may have to do, but in the best world we would like to have it say the Fund name and the YTD for that fund.  Some people only give to one fund, some people give to multiple funds.  
So instead of saying:
Jim Smith 123 First Ave 1200.00 11 transactions  
I'd like it to say:
Name       Address            Fund YTD                      Fund YTD                         YTD               Transactions
Jim Smith 1234 First Ave 350.00 for John Smith 250.00 for Steve Smith  600.00          11

I have two Excel files attached, with just one example. I have many many people this would go out to.  So I guess I need to know if I can combine information from the files, and get rid of information from the multiple line file. Not sure if I'm explaining this well enough?  If it can be done that would be great!?

Please ask any questions you might have, and you can even tell me I'm ridiculous, lol.

Thank you,
Lisa
MulitpleLines.xlsx
SingleLine.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.

NorieVBA ExpertCommented:
Can you attach a workbook with sheets that show/explain 'before' and 'after' representations of what you have/want?
0
lisafamfAuthor Commented:
I'll try, but I'm not sure anymore about this, lol. I think it's impossible.
MulitpleLines.xlsx
0
NorieVBA ExpertCommented:
Do you want to keep the first 9 columns (A-I, Account No - Postal Code) and then for each transaction repeat the same set of columns (eg Fund, YTD for Fund etc) for each transaction across the same row?

Is all the data in one file?
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:
Yes, that is what I want.  So each account number has only one row, with all the data below in the one row, cosolidated.  Does that make sense?
0
NorieVBA ExpertCommented:
It makes sense and it's probably not impossible but I think we might need a clearer example (or two).:)
0
lisafamfAuthor Commented:
LOL, so hard, because  there are multiple scenarios, and I have to change all the data to protect the innocent, lol.  Can't share our donor database.  It would be so easy to do that.  Suggestions?  Thanks so much for taking  your time too!!! :)
0
Martin LissOlder than dirtCommented:
Questions:
  1. The desired results show two funds. Where does the second one come from?
  2. On your sample sheet cell M19 says "Steve Smith". Should that be "John Smith"?
0
NorieVBA ExpertCommented:
We don't need to see any real data, just something that's representative of what you actually have.

You kind of got that in the second MultipleLines workbook you attached but it didn't seem consistent, to me any way.
0
lisafamfAuthor Commented:
ok, I'll try again.   Line 49 is what I need out of the first 46 lines.  Thanks Again!
LIsa
ForExpertsExchange.xlsx
0
NorieVBA ExpertCommented:
Lisa

I can think I can see what you are after now and I'll try and get back to you later with something.

One thing though, I don't see where the column YTD Non is coming from.
0
lisafamfAuthor Commented:
Thanks, Norie!  I labeled that wrong....it's not YTD Non, it's just Non-Deductible Amount
Sorry about that!
0
Ejgil HedegaardCommented:
Try attached file.
Lisafamf-Single-line-list.xlsm
0
lisafamfAuthor Commented:
Ejgil, this seems perfect.  I'm home sick today, but can you give me the information on how to set this up? Or do I just look at the  macro.  Our whole file is about 42,000 lines long.  This is amazing, I think?  My brain is foggy from being sick, lol
0
Ejgil HedegaardCommented:
The macro use the sheet names Sheet1 as input, and "Single line" as result.
Just copy your data to Sheet1, and run the macro.

The columns must be as in the sample, and the list sorted on Account number in column A.
The macro could do that if needed.

If it is too slow, it can be changed to process data in arrays in VBA, and write all to the sheet at the end.
That requires quite a lot of changes to the code, but can reduce process time significantly.
Sorting on Fund also would help,because the list can be processed from top to bottom, and some loops can be avoided.
0
lisafamfAuthor Commented:
Thanks, Ejgil!
A few things.  I have that extra column, B.  Can the macro be rewritten without that column.  Not sure why I put it in there.  Also, I would like the sort to be on the account number.  Is it best to have the  macro do that?  Because my brain is still foggy, I'm not sure what  you mean by "list sorted on Account number in column A"?  If it isn't run by the macro is there something I need to make sure I do with the account numbers?  Sorry to seem dense!
Very appreciative,
Lisa
0
Ejgil HedegaardCommented:
New version attached.
Column B deleted, so data range are one region.
Sort on "Account Number" and Fund.
Working with arrays to speed up the process.
Some comments added to the code.
Lisafamf-Single-line-list-A.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:
That new file worked great speed wise!!!  Thank you soooo much.  Looks like I am good to go.  I immensely appreciate all the help, and the solution to this issue!
0
lisafamfAuthor Commented:
Awesome job to something I thought was impossible!!!!!!!
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
Databases

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.