Does anyone have any idea how to do a comparison of 2 worksheets:
Original source – from supplier
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.
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]
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 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…