Excel comparison -- different worksheets -- prices

Hi everyone,



Does anyone have any idea how to do a comparison of 2 worksheets:


  1. Original source – from supplier


  2. Current sheet used to convert to csv for upload.



    For updated prices & new items added from supplier.



    Please enlighten.



    Thank you.

[quote name=‘ghgames’]Hi everyone,



Does anyone have any idea how to do a comparison of 2 worksheets:


  1. Original source – from supplier


  2. Current sheet used to convert to csv for upload.



    For updated prices & new items added from supplier.



    Please enlighten.



    Thank you.[/QUOTE]





    Not sure how to compare two worksheets but, using one worksheet with information from both in it, you could use VLOOKUP.



    To find out new items, put your current list of part numbers in column B, either the word “yes” or a description in column C. In column E put the supplier’s list of part numbers, which will include the new items. In column F, your formula will be:



    =vlookup(e1,$b$1:$c$2000,2,false)



    Substitute $c$2000 for the amount of rows you have in columns B and C. This will return, in column F, either a value (you already have the product in your list) or N/A, which means you don’t have the product in your list. You can then add all the products with N/A next to them.



    To update your pricing, put the supplier’s part numbers in column B with the prices in column C. Put your current list in column E and use the same formula, which will return the new pricing.



    Hope this makes sense - VLOOKUP can be used for lots of different things which you will find useful for updating your product list.

That’s a great idea.

Hi Lyn,



Thank you for your headsup.



But as I’m very new to vlookup function… I’m slightly lost…



Thing with supplier list is – they do not provide part numbers – only product name and the prices (this is sent weekly)



So how do I compare this list with the current list i use for upload?



I have a supplier list with part numbers but those are in Aug 2010 for instock items only. Should I use this to compare to the new list and then compare to the upload list?



Maybe a sample list here will help me to understand it further?



I have attached a sample list with 3 tables – and assume all three are in different sheets as per in my brackets…



How can I compare the tables? Btw, the weekly list might even contain out of stock or discontinued products which I do not want to include but have to sift them out, anyway to do that? (given there’s no remarks whatsoever)



Thank you in advance!


[quote name=‘Lyn’]Not sure how to compare two worksheets but, using one worksheet with information from both in it, you could use VLOOKUP.



To find out new items, put your current list of part numbers in column B, either the word “yes” or a description in column C. In column E put the supplier’s list of part numbers, which will include the new items. In column F, your formula will be:



=vlookup(e1,$b$1:$c$2000,2,false)



Substitute $c$2000 for the amount of rows you have in columns B and C. This will return, in column F, either a value (you already have the product in your list) or N/A, which means you don’t have the product in your list. You can then add all the products with N/A next to them.



To update your pricing, put the supplier’s part numbers in column B with the prices in column C. Put your current list in column E and use the same formula, which will return the new pricing.



Hope this makes sense - VLOOKUP can be used for lots of different things which you will find useful for updating your product list.[/QUOTE]

Sample - HK.zip

Also, do I need to sort the tables such that they are of the same order in terms of category and product name for all the tables?

It looks like you will need to use the product name rather than the product code, which could cause a problem because they would have to be exactly the same in each list with no extra spaces or full stops or anything.



I have attached a very rough sample for you. For the pricing, you will need to do it for each variation but you can then transfer the values to a new .csv for import into CS-Cart.



There is no way I can think of that is going to do it for you without some work on your part but this is the method I use for updating pricing, adding new products, etc., and with a database of more than 50,000 products, it works fine for me.

sample.zip

[quote name=‘Lyn’]It looks like you will need to use the product name rather than the product code, which could cause a problem because they would have to be exactly the same in each list with no extra spaces or full stops or anything.



I have attached a very rough sample for you. For the pricing, you will need to do it for each variation but you can then transfer the values to a new .csv for import into CS-Cart.



There is no way I can think of that is going to do it for you without some work on your part but this is the method I use for updating pricing, adding new products, etc., and with a database of more than 50,000 products, it works fine for me.[/QUOTE]



Ok, Thanks for the tips!



Will try your method, but looks like I’ll need to sort the tables properly first… arrgh…

They don’t need to be in any particular order - you will get a result no matter what order the columns are in.

ok, thanks Lyn.



Am trying them now…



Really tedious without the Product code… some items I renamed even the original name! haha



Was thinkign fi anyone uses MS Access to manage this and update the new prices…

www.compareandmerge.com



may help you



John

[quote name=‘johnbol1’]www.compareandmerge.com



may help you



John[/QUOTE]





hmm don’t really know how to use this…



Thinking of using Access to update the items… Hope it works!



Vlookup, is still slightly tedious for me… :slight_smile: