Excel 2007 : Returning actual cell values from the array of index and the look-up of match

Morning all,

I have created a catalog price and stock updater with index match. Basically the new catalog has the formula to match the part numbers and update the prices irrespective of what order the catalog or pricelist is in.

for the part number

Please Login or Register  to view this content.

for the price

Please Login or Register  to view this content.

example.xlsx

There are 2 errors.
1 - when the part no longer appears on the pricelist
2 - when a new part appears on the pricelist

Could someone point me in the direction of what functions to use to solve this?

1 - when the part no longer appears on the pricelist, to return the part name from the old catalog with a 0 as stock
2 - when a new part appears on the pricelist, to return the new part name with its price and 1 as stock

I would appreciate a bit of direction as to what formulas could be used?
Many thanks
~W

You'd have to setup your tables differently.
Currently, there's no way of knowing if a part is no longer on the list or is new. If you can have a secondary table where you list EITHER new OR old products, then a comparison could be done.

I have taken the liberty of creating that extra table.
The formulae have all been adjusted, this should work fine.

Remember to mark your thread solved and to use the little star on the left to thank those who help

hi dip11, thanks for your input. Shew my brain is fried after 2 days on this, nesting logic not my strong point
I dont understand when you say a secondary list with either the new products or the old, as the pricelist is the new and the catalog the old?

I came up with this before i saw your reply, it returns the new product not on the old list.

Please Login or Register  to view this content.

You have a mix of formulas in the table, im unsure as to what goes where?

Please Login or Register  to view this content.

Sorry, my mistake, I made an error.

This is correct, put in New catalog sku:

Please Login or Register  to view this content.

This was incorrect, put this in New catalog price instead:

Please Login or Register  to view this content.

The reason I created the 4th table for unused (old catalog not in new pricelist) is that otherwise you can't differentiate whether a sku is completely new or just obsolete for puposes of returning the price as actual, 1 or 0.

Hey, D

Thanks its starting to make sense now.

Put in new SKU

Please Login or Register  to view this content.

Should this not be

Please Login or Register  to view this content.

because A5 is where the new sku number is?

It doesn't matter actually. That formula is just there to make sure the sku field is filled out.

I dont understand.

If the new sku from the pricelist is to be added into the new catalog together with its price and isinstock as 1 (or true) wouldnt that matter? or do I do a different equation for this?

Do you think this would work for the new sku and price, I have tested it and it seems to be ok?

new sku

Please Login or Register  to view this content.

price of new sku

Please Login or Register  to view this content.

Well the above code didn't work but I managed to solve it. Thanks Dip11, without your input I wouldnt have completed this. I thought someone might find this useful in the future so posting the completed formulas.

I am just using an "is in stock" function in my backend so dont need quantity, just 1 or 0. I set the "is in stock" to <=1 because I like to be ahead of the suppliers

New price and stock list where:
column A is sku
column B is price
Column C is stock qty

Catalog needing updating where:
Column D is sku
Column E is price

The formulas will search through the old catalog and new pricelist irrespective of sku order and if a price or stock has changed will adjust it.
It will remove sku's that dont appear in the new pricelist and add new ones that do with their price and stock.

sku column

Please Login or Register  to view this content.

price column (with markup and vat added if you need it)

Please Login or Register  to view this content.

stock column

Please Login or Register  to view this content.

If you want a bit of detail then the following might be useful too.

end of life items no longer in pricelist:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

~W