create summary file from multiple files

anis

Board Regular
Joined
Dec 28, 2004
Messages
87
greetings,

i'm on a search of an efficient way of creating a summary files from another 8 files. First let me explain:

1) Source files will be named - Jan_Sum.xls, Feb_Sum.xls, Mac_Sum.xls, etc
in the My Document folder
2) Output file(summary) will be named - Jan_Summary.xls, Feb_Summary.xls, etc. and will be saved into My Document folder as well.

Here's wut the data would look like:

DEC file:
Book1
BCDEF
2FRUIT
3typeMar-06Apr-06May-06
4orangeDemand764580
5Capacity100100100
6Loading %76%45%80%
7
8bananaDemand101340
9Capacity505060
10Loading %20%26%67%
11
Dec


JAN file:
Book1
BCDEF
14FRUIT
15typeApr-06May-06Jun-06
16orangeDemand467057
17Capacity100100100
18Loading %46%70%57%
19
20bananaDemand53240
21Capacity505060
22Loading %10%64%67%
23
Dec



These are the sample input files
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

anis

Board Regular
Joined
Dec 28, 2004
Messages
87
here's the sample output file

Sample summary chart for JAN_SUMMARY
Book1
BCDEFGHIJ
27Nov-05Dec-05Jan-06Feb-06Mar-06Apr-06May-06Jun-06
28Orange
29Mayxxxxxxxxxxxxxxxx
30Junxxxxxxxxxxxxxxxx
31Julxxxxxxxxxxxxxxxx
32Augxxxxxxxxxxxxxxxx
33Sepxxxxxxxxxxxxxx
34Octxxxxxxxxxxxx
35Novxxxxxxxxxx
36Dec764580xx
37Jan467057
38
39Banana
40Mayxxxxxxxxxxxxxxxx
41Junxxxxxxxxxxxxxxxx
42Julxxxxxxxxxxxxxxxx
43Augxxxxxxxxxxxxxxxx
44Sepxxxxxxxxxxxxxx
45Octxxxxxxxxxxxx
46Novxxxxxxxxxx
47Dec101340xx
48Jan53240
Dec



OK, so here's go the logic that i've come up wif. the summary file will be created when the source file for that month is created. i've completed the code for the template and create new file save the file and everything. but now i'm a bit lost in the logic for copying the values of demand from every other file. i'm wondering is the logic i have here would be an efficient one to be coded or there's more efficient way of producing the same output?

- activate main source file ( i.e if user is to create a JanSummary file, Jan source file would be the main source file)
- search for the particular fruit in col B
- check if the 1st month of the chart = or > 1st month of the output chart
Yes --> copy data from 1st month, activate output file, search where the month's col is, set the col to a variable (eg: CL). search for the particular fruit in col A, go to the row of the source file's month(obtain from the source file name), set the row of the source file's month in the range of the particular fruit to a variable (eg: RW) . Paste the copied values to (RW&CL)

No --> get 1st month of the output chart, check in the input file for the month, get the col of the month. copy the values for the fruit's demand starting from the month till last data. activate output file, search for the particular fruit in col A, go to the row of the source file's month(obtain from the source file name), set the row of the source file's month in the range of the particular fruit to a variable (eg: RW) . Paste the copied values to (RW,2)

- loop for the previous 8 months file. (i.e if Jan is main source file, loop for Dec until May source files)
 

anis

Board Regular
Joined
Dec 28, 2004
Messages
87
hmph...confused as well? do help~~

i think that i'm thinking too much now~~~ :oops:
hope there's sumone who can help me sort this a bit and makes it more clearer cos im definitely confused with my own logic at this moment. :rolleyes:
THANX a lot in advance...

best regards,
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,621
Members
414,081
Latest member
Subaru_Steve

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top