Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Excel 2007 data comparison


(!)

Lucas2000's Avatar
Lucas2000 Lucas2000 is offline
Member with 2 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Intermediate
19-Jul-2012, 03:52 PM #1
Solved: Excel 2007 data comparison
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's Avatar
20_2_Many 20_2_Many is offline
Member with 497 posts.
 
Join Date: Jun 2012
19-Jul-2012, 06:33 PM #2
One method
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.
Attached Files
File Type: xls PriceCompare.xls (27.0 KB, 60 views)
getthecansout's Avatar
getthecansout   (Ian) getthecansout is offline
getthecansout has a Photo Album
Computer Specs
Member with 183 posts.
 
Join Date: Jan 2011
Location: Ireland
Experience: Beginner
20-Jul-2012, 03:22 PM #3
Price compare
You could also swap the old Vendor sheet with the new one from your supplier.
The Business sheet will update itself.
Attached Files
File Type: xls PriceCompare.xls (29.0 KB, 41 views)
getthecansout's Avatar
getthecansout   (Ian) getthecansout is offline
getthecansout has a Photo Album
Computer Specs
Member with 183 posts.
 
Join Date: Jan 2011
Location: Ireland
Experience: Beginner
20-Jul-2012, 03:28 PM #4
Ive slightly altered 20_2_manys abit
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's Avatar
Lucas2000 Lucas2000 is offline
Member with 2 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Intermediate
21-Jul-2012, 12:12 PM #5
Thanks guys,

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

Lucas
20_2_Many's Avatar
20_2_Many 20_2_Many is offline
Member with 497 posts.
 
Join Date: Jun 2012
21-Jul-2012, 05:28 PM #6
Good Luck with it. Remember to mark this Solved.
getthecansout's Avatar
getthecansout   (Ian) getthecansout is offline
getthecansout has a Photo Album
Computer Specs
Member with 183 posts.
 
Join Date: Jan 2011
Location: Ireland
Experience: Beginner
25-Jul-2012, 02:17 PM #7
Hope that works ok
Any more problems with it give us a shout and good look with the business
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
data comparison, excel 2007

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑