H
hamed_gan
Dear all,
I have a problem which I cannot easily solve and It's giving me
headache.
The situation is as follows;
* I have 72 files, each representing the sales of item per Month ove
the last 6 years. Each of these files containes 4 columns.
Column 1: Customer Number;
Column 2: Item number; (only the item numbers which have been sold i
the respective month are in this column)
Column 3: Product Line; Item numbers have a product line. There are 1
Product lines
Column 4: Sales of the representing month
* I have 1 file containing ALL the product items/range of my company.
Column 1: Item Number (again, ALL the product lines) (approx. 10,00
Items)
Columm 2: Product Line
I want to do the following;
When I type in a customer number, and a specific period I want Excel t
perform the following actions;
1st. Identify which Items have been sold to my customer, in the give
period.
2nd. Give the sales of the individual items per month to my give
customer. So, if the preiod is Jan-2004/July-2004, I want excel to giv
the monthly sales of the given customer within the given period in
specific column.
3. Combine the items which have been sold to the customer to the fil
containing ALL THE ITEMS of my compnay. This is neccessary as I need t
know which Items have not been sold to a customer.
All this data has to be represented in a Master File, so all the file
are external.
I have tried several ways.
The 3rd action I already had an idea about; use the sum function wit
multiple criteria. =SUM((CUSTOMER NUMBER)*(ITEM NUMBER)*(GIVEN MONTH)
This approach is however process intensive and Excel just can't handl
it within a reasonable time. It has tocalculate 72 Columns * 10,00
Rows = 720,000 cells, and this takes ages.
The 1st and 2nd actions I can also manage using, OFFSET, MATCH, HLOOKU
, VLOOKUP, IF() and some others. However, this is also a time consumin
action for Excel to perform on 720,000 cells.
I also tried to use PivotReports, to combine the 72 files and tha
perform my calculations, but PivotChart just doesn't do what I want.
also cannot copy and paste the sales columns from all the files int
one file, because the Items which have been sold to a customer differ
each month.
With other words, I'm stuck.
I have limited knowledge about VB-langauge, but I'm good in Javascript
However, I want to perform this with Excel. Any suggestions would b
really welcome.
Regards,
Zurvy :
I have a problem which I cannot easily solve and It's giving me
headache.
The situation is as follows;
* I have 72 files, each representing the sales of item per Month ove
the last 6 years. Each of these files containes 4 columns.
Column 1: Customer Number;
Column 2: Item number; (only the item numbers which have been sold i
the respective month are in this column)
Column 3: Product Line; Item numbers have a product line. There are 1
Product lines
Column 4: Sales of the representing month
* I have 1 file containing ALL the product items/range of my company.
Column 1: Item Number (again, ALL the product lines) (approx. 10,00
Items)
Columm 2: Product Line
I want to do the following;
When I type in a customer number, and a specific period I want Excel t
perform the following actions;
1st. Identify which Items have been sold to my customer, in the give
period.
2nd. Give the sales of the individual items per month to my give
customer. So, if the preiod is Jan-2004/July-2004, I want excel to giv
the monthly sales of the given customer within the given period in
specific column.
3. Combine the items which have been sold to the customer to the fil
containing ALL THE ITEMS of my compnay. This is neccessary as I need t
know which Items have not been sold to a customer.
All this data has to be represented in a Master File, so all the file
are external.
I have tried several ways.
The 3rd action I already had an idea about; use the sum function wit
multiple criteria. =SUM((CUSTOMER NUMBER)*(ITEM NUMBER)*(GIVEN MONTH)
This approach is however process intensive and Excel just can't handl
it within a reasonable time. It has tocalculate 72 Columns * 10,00
Rows = 720,000 cells, and this takes ages.
The 1st and 2nd actions I can also manage using, OFFSET, MATCH, HLOOKU
, VLOOKUP, IF() and some others. However, this is also a time consumin
action for Excel to perform on 720,000 cells.
I also tried to use PivotReports, to combine the 72 files and tha
perform my calculations, but PivotChart just doesn't do what I want.
also cannot copy and paste the sales columns from all the files int
one file, because the Items which have been sold to a customer differ
each month.
With other words, I'm stuck.
I have limited knowledge about VB-langauge, but I'm good in Javascript
However, I want to perform this with Excel. Any suggestions would b
really welcome.
Regards,
Zurvy :