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
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
Can you attach a workbook with sheets that show/explain 'before' and 'after' representations of what you have/want?
ASKER
I'll try, but I'm not sure anymore about this, lol. I think it's impossible.
MulitpleLines.xlsx
MulitpleLines.xlsx
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?
Is all the data in one file?
ASKER
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?
It makes sense and it's probably not impossible but I think we might need a clearer example (or two).:)
ASKER
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!!! :)
Questions:
- The desired results show two funds. Where does the second one come from?
- On your sample sheet cell M19 says "Steve Smith". Should that be "John Smith"?
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.
You kind of got that in the second MultipleLines workbook you attached but it didn't seem consistent, to me any way.
ASKER
ok, I'll try again. Line 49 is what I need out of the first 46 lines. Thanks Again!
LIsa
ForExpertsExchange.xlsx
LIsa
ForExpertsExchange.xlsx
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.
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.
ASKER
Thanks, Norie! I labeled that wrong....it's not YTD Non, it's just Non-Deductible Amount
Sorry about that!
Sorry about that!
Try attached file.
Lisafamf-Single-line-list.xlsm
Lisafamf-Single-line-list.xlsm
ASKER
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
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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
ASKER
Awesome job to something I thought was impossible!!!!!!!
ASKER
Hi Ejgil,
I was wondering if you could help me with some updates to this macro? You helped me with another report that are the same kind of things I need to change with this, and few other things. Are you available to help? Thanks!
Lisa
I was wondering if you could help me with some updates to this macro? You helped me with another report that are the same kind of things I need to change with this, and few other things. Are you available to help? Thanks!
Lisa