[Lucas2000]

Hi there,

I hope you can help. I’m about to start an internet business with a friend and get new price amendments from the wholesaler every couple of months.

What I want to know is;


We have around a 1000 products, the supplier has about 10,000 and they can supply us with an Excel doc every couple of months. Is there a way of finding out under product code which products have changed in price in our range of 1000 products, if I were to put one on one tab and one on another? We wouldn’t want to know about the other 9000 products, if this is possible?


EG.



Column A

Product code



Column B

Product description



Column C

Price Ex VAT

[20_2_Many]

It is very possible to do this in Excel, although other programs like Access may be better suited. Governing factors are ease and output. In the attached workbook, the Business tab (you) compares the Vendor Product Number, and does the math for the price difference. The Vendor sheet has more products than the Business sheet.
=INDEX(Vendor!$C$2:$C$7,MATCH(Business!$A$2,Vendor!$A$2:$A$7,0))
Index (VENDOR PRICES) Match(PRODUCT CODE per Row with VENDOR PRODUCT CODE) using Exact Match. This returns the Index value which is the Price from the New Vendor List.
Ideally for something like this where formulas can get trampled, I would have a Vendor workbook, A Business Workbook, and a Compare workbook.The Compare workbook would be the one that you open. It would automatically save itself with a new name (Protecting its formulas), import the Business list of the Products you have, then import the Vendor list.Importing from the closed sheets preserves their integrity, and if everything goes bad, opening Compare again will recreate the new workbook in just a few seconds.
This is not the best method for what you need, but if Excel is what you know to use, it works as an interim while perhaps learning access or SQL and data queries and the like.
This Compare workbook is not perfect, it is a demo.

[getthecansout]

You could also swap the old Vendor sheet with the new one from your supplier.
The Business sheet will update itself.

[getthecansout]

But it should work as long as the new sheet from your suppliers is laid out the same as the old one i.e

the sheet must have the same name ie both are called stock sheets or whatever you call them also
red pencil is in a1 quantity is in a2 and price is in a3 on BOTH sheets then it will recalculate


Hope that works for you

[Lucas2000]

Thanks guys,

Really appreciate your help, I've checked out the formulae and that should apply well to the spreadsheets!

Lucas

[20_2_Many]

Good Luck with it. Remember to mark this Solved.

[getthecansout]

Any more problems with it give us a shout and good look with the business