G
Guest
Guest
Need help combining 2+ Excel files (can be in same worksheet or different ones) so that they match up automatically. Most be done every month. Files originally come from 4+ sources (Excel Pivot Table, Accounting database program, Excel worksheets) and must line up by the primary field. The original doc may have i.e., 4500 rows, ~15 fields. May need to pull in info from the corresponding files by one or more columns but they must match up with the original doc, i.e., in the original doc, there may be an ID # 1000, but in the Pivot Table there's no value for ID #1000, but in the Accounting program there may or may not be a value for ID #1000 and in the two different Excel files, one may have a value for ID #1000 but the other won't. This process needs to be as automated as possible in order to accomplish this task every month.
Currently, I have one doc with all the fields, however, every month, at least one new field will be added (i.e., Jan., Feb., Mar., etc.). Then at least 3 columns in the original doc. need to be updated with info. from the external sources, matching up with the ID#s from each.
There are calculations that need to ultimately be made, but I have only been able to manually add those in at the end once all the importing has been accomplished. Additional manual manipulation has to occur since when the info is imported, some fields are not in the correct form, i.e., a currency field with no ending zeros is a text field in one of the sources or it has ending characters that must be removed before the final processing.
I have so far been getting all of the info, converting it to Access (though I'm not sure why I'm doing that exactly) and then dumping it back into Excel to manually manipulate. I have considered macros but can't seem to figure it out.
i.e., Now that I have the original doc. (I have two original docs., one with calculations and subtotals and one without), when I get the new months data, I need to say something like: when column T2 is the same as column C2, enter column S2 into column Q2, if column T2 is not the same as column C2, enter $0 (in the form of $ -). Then, in another dump, I have an Excel doc. with 2 or 3 columns (1 of which is the primary field in the orig. doc.) and I need the 3rd column to match up according to the primary field in the orig. doc. Then I need the same type of process to occur with a different Excel doc. and then with an Access file.
Again, this needs to be accomplished on a monthly basis as automated as possible.
Can anyone help. I can be reached by phone to talk with you if that's easier. Although I'm sure there's someone out there who can decipher this much easier than I can.
Thank you so much.
Currently, I have one doc with all the fields, however, every month, at least one new field will be added (i.e., Jan., Feb., Mar., etc.). Then at least 3 columns in the original doc. need to be updated with info. from the external sources, matching up with the ID#s from each.
There are calculations that need to ultimately be made, but I have only been able to manually add those in at the end once all the importing has been accomplished. Additional manual manipulation has to occur since when the info is imported, some fields are not in the correct form, i.e., a currency field with no ending zeros is a text field in one of the sources or it has ending characters that must be removed before the final processing.
I have so far been getting all of the info, converting it to Access (though I'm not sure why I'm doing that exactly) and then dumping it back into Excel to manually manipulate. I have considered macros but can't seem to figure it out.
i.e., Now that I have the original doc. (I have two original docs., one with calculations and subtotals and one without), when I get the new months data, I need to say something like: when column T2 is the same as column C2, enter column S2 into column Q2, if column T2 is not the same as column C2, enter $0 (in the form of $ -). Then, in another dump, I have an Excel doc. with 2 or 3 columns (1 of which is the primary field in the orig. doc.) and I need the 3rd column to match up according to the primary field in the orig. doc. Then I need the same type of process to occur with a different Excel doc. and then with an Access file.
Again, this needs to be accomplished on a monthly basis as automated as possible.
Can anyone help. I can be reached by phone to talk with you if that's easier. Although I'm sure there's someone out there who can decipher this much easier than I can.
Thank you so much.
