I currently have a large amount of data that I need to transfer to a master detail file. This master file contains additional formulas with which I use to summarise the data.
I have been asked to update this information on a monthly basis. (The master detail file can be as long as 23,000.00 lines). I get monthly sales information from our data warehouse. The information is laid out as follows on both files:
Master file -
Customer Name
Product Code (I have added all possible codes that the customer could purchase)
Qty
Sales
Cost
Monthly Worksheet from data warehouse -
Customer Name
Product Code (This file only details the codes with sales for that month)
Qty
Sales
Cost
This information is repeated for over 90 different customers.
I know how to do a basic vlookup based on one common identifier.
I need to take the lookups one step further by doing the following -
1. Match the customer names in both files (note - customer name in the data warehouse file may not been on the same row as the month before)
2. Match the products codes that are associated with the particular customer to then pull over the sales data.
This is where I reach the limitations of my excel knowledge. I have tried to cut and paste the information I need onto the master file but this just takes too long!
Would anyone be able to suggest a formula or macro/vba code that I can use within excel to automatically pull in the information I need?
Thanks,
Kabeez
I have been asked to update this information on a monthly basis. (The master detail file can be as long as 23,000.00 lines). I get monthly sales information from our data warehouse. The information is laid out as follows on both files:
Master file -
Customer Name
Product Code (I have added all possible codes that the customer could purchase)
Qty
Sales
Cost
Monthly Worksheet from data warehouse -
Customer Name
Product Code (This file only details the codes with sales for that month)
Qty
Sales
Cost
This information is repeated for over 90 different customers.
I know how to do a basic vlookup based on one common identifier.
I need to take the lookups one step further by doing the following -
1. Match the customer names in both files (note - customer name in the data warehouse file may not been on the same row as the month before)
2. Match the products codes that are associated with the particular customer to then pull over the sales data.
This is where I reach the limitations of my excel knowledge. I have tried to cut and paste the information I need onto the master file but this just takes too long!
Would anyone be able to suggest a formula or macro/vba code that I can use within excel to automatically pull in the information I need?
Thanks,
Kabeez