Automating price file creation for multiple customers

First time posting, and this issue kind of covers general help, tables, formulas, and maybe macros, so not sure where was the best place to post it.

I am trying to manage price files for multiple customers. In this example there are 2 main price files, and each customer could be on both price files dependant on product group. i.e. Price file 1 for some products and price file 2 for other products, which can be seen in the "Customer Master" tab in the (hopefully) attached file and below.

	Pears	Bananas	R Apples	G Apples
Adam	1	1	2	1
Bill	2	2	2	1
Charlie	1	2	2	2

The next tab shows the actual price files, with different items having different prices based on which price file it is.

Each month the price files will be updated, and certain prices will be tweaked and amended. This then needs to be reflected against each customer, so that there is an up to date price file for each one.

What I'm looking for is some form of automation, but not sure the best way to go about it.
In an ideal world, the end result would be something along the lines of:

• The manager updates the price files in the Pricing tab at the end of each month based on marketing information etc.
• Based on which price files the customer has listed for the different product groups in the customer master tab, a full price file for that customer is created.

We would then have price files for all the customers which we could load into our ERP system. Doing that manually would be possible, but likely to result in mistakes and would take a long time especially with lots of customers. It would just involve looking at what price file they have for a certain product group, and then copying and pasting the relevant item numbers and prices into that customers price file. So cumbersome and very open to errors. Or alternatively (and even more time consuming), manually changing each customers price file one by one.

Not sure if this is possible through excel, but even part automation of the process would be fantastic and save huge amounts of time. Especially if there is a solution that is scalable, over many customers and many items. Not sure what it requires though, as in I have no idea where to even start, hence wasn't sure where to post it. Hoping that someone can point me in the right direction as to what I should be thinking of doing! My excel knowledge is good when it comes to tables/formulas etc, but pretty lacking with regards to macros, but happy to learn if it's going to save me time!

Thanks

Hi dflak, thanks for the response.
1) Price files 1 and 2 are in essence two completely different price files. For example price file 1 may be for customers in northern Ireland, and price file 2 for customers in the Republic of Ireland. But currently for any one customer, they may be using different parts of both price files. So a customer may be on price file 1 because they are based in NI. But if they are struggling to compete with a certain product group (say pears), and that product group is cheaper in price file 2, we may put them on price file 1 for all products, except for the "pears" product group where we will put them on price file 2. Each month both price files may be reviewed and edited based on market conditions.

2) Not sure what you mean by date information, but lets say that the price files are valid from today for the next 5 years, for example

3) So there are product groups, and within that group each type of product has a different item number. Maybe fruit was a bad example to use, but I wanted to generalise the information. For the fruit example, there are multiple types of red apples, so each different type would have a different item number. But all of the "AR" item numbers all fall under the "Red Apple" product group. The reason for setting it up like this is: the prices are typically determined at an item level number, but the entire group will be assigned to a customer. So in the example above, the customer will get price file 2 for the entire Red Apple product group.

So in general, a customer can purchase any of the items. Each item falls under a different product group. And we manage the price files at an item level, but then set the prices for the customer by product group. This is because we have only a few prices files, but over a hundred customers. So it is easier to manage a few price files, rather than a separate price file for each of a hundred customers.

Hope that clarified things. It's all very clear in my head, but it's a harder job putting it into words, so apologies if things aren't that obvious.

So the reason I don't think I need dates is that I'm not looking for this to be a running tracker of orders. Instead I want it to be a combined price file for all customers. Something along the lines of what is attached
In either format really. But the key here is that each customer can be on multiple price files, dependant on the product group.

So the customer master needs to be a matrix, because each customer can be on a different price file for a different product group.

And for the mapping. The pricing is against the item number, so if they order item number P100 they get a certain price. The product groups are just a way for us to categorise and manage the data. So a customer would never order "pears," they would order a specific item number.
And then when we are for example analysing sales, we could see 5 P100's sold, and 3 P101's sold, so in total 8 pears.
And similarly, if doing a price review, we may say the price for all pears in Price file 1 is going up 5%, and in price file 2 it's 10%. Easier to manage multiple price files that way, rather than updating each price individually for every customer.

What you've put together is excellent, so thanks very much! But I guess the next step from there is whether the lookup can be against multiple criteria.
So for Customer 1, for this part number, if the product group is A, then it pulls the prices according to that price file?

OK so hopefully this is much easier to understand.
On the left is the price files which will be updated each month.
Based on the matrix on the top right (which determines which price is used for a certain product group for a certain customer), for Item P100, as it is part of the "Pears" product group, Adam should have a price of £3.60, whereas Bill should have a price of £3.45. Because Adam is on Price 1 for Pears, and Bill is on Price 2.
So those amounts need to be in the highlighted cells based on the previous 2 tables.

Is that doable? And extendable across many customers.