Update pricing and mark discontinued items for master price list

I've got 2 sheets.

First sheet is my own master price list.
Second one is Supplier "Fruit Co." price list.
Third one is Supplier "Produce Land" price list

Every year the supplier's will change their price list. Sometimes they will add items, sometimes they will remove some and sometimes they will change the pricing.

I only have 1 sheet for each supplier, so if the supplier introduces a new price list, I clear their current one and paste in the new one. The formatting remains each time.

I would like to rig something so that if the master file has the same part number and supplier as another sheet, it will look to see if the cost price has changed, and if it did, the it will change the price on the master file.

Also, if the product is NOT to be found on any of the sheets, then the product will be marked "disco" followed by the year indicated in the spreadsheet's effective date column.

Only problem, my actual sheet (this is just an example) is uber complex and has a crazy VBA running in the background. I was hoping to use vlookup in the master file's cost column and disco column to update it, rather than VBA.

Is someone able to rig the example spreadsheet to work so I can try and apply the changes to my ultra complex one? (hope my attachment worked?)

Thanks!

On the sheet "Master" created three new columns with the following information:
Column G :: MyDiscoRow is the product line in the supplier's sheet
The formula is

Formula:

=IFERROR(MATCH($B2,INDIRECT("'"&$A2&"'!$B$2:$B$"&COUNTA($B$2:$B$5000)),0),"")

Column H :: NewDisco is the new situation of the product
The formula is

Formula:

=IF(ISNUMBER(G2),"",IF(F2<>"",F2,"Disco "& INDIRECT("'"&$A2&"'!$E$2")))

Column I :: NewCost is the new cost (independent of having been changed)
The formula is

Formula:

=IF(ISNUMBER(G2),INDEX(INDIRECT("'"&$A2&"'!$D$2:$D"&COUNTA($B$2:$B$5000)),G2,1),"")

This example sets in 4999 the number of supplier by product ($B$2:$B$5000)
I attached the file with the changes I made.

We can point out at every supplier which are the new items using the following formula to each of the suppliers sheets in a new column (E)

Formula:

=IF(OR(A2&B2=Master!$A2:$A$5000&Master!$B2:$B$5000),"No","Yes")

For new items we can copy and paste the new information on the sheet "Master"

Thanks all for the posts. Newdoverman, good job on the worksheet :-)

Your example is great, only in our situation its a little different. You see, the master file is actually created one by one, as we need the parts. So the suppliers might have 50,000 items between their price books, but we only enter the ones we actually want to sell. The master file might only have 500 items on it.

But, because the suppliers have so many parts, weeding through their new price list to find out if the items price has changed or is not on the list (means it was discontinued) is alot of work.

I've attached the actual file we are working with. May as well, instead of playing with the apples and oranges sheet :-) There is a complex VBA in the background what generates product codes in column F&G.

Can anyone get column "U" to update price based the supplier tabs (other workbooks expressed by the supplier names). If the price is changed, update Column U, regardless if it went up or down. I'm not interested in keeping history on price changes. For that I can pull up old sheets.

For column AU, if the product code can't be found in any of the supplier tabs, to show "Disco XX/XXXX" (where XX/XXXX is the current date expressed in Month/Year in excel that the entry was made into the cell) in Column AU.

Hope this is do-able. I'm thinking with v-lookup in Column U to look through the supplier tabs and find the price searching by supplier code (column T) and supplier name. Codes are unique, but incase the suppliers have duplicates, the supplier name would be the unique identifier. If no price, N/A shows.

Same, in column AU. Use v-lookup to check supplier tabs to find if the product code is listed anywhere (match to supplier name as well as code). If its not, mark it as Disco and the date. Not sure if VBA is required for this?

Thanks all for your help!

Hi Newdoverman,

Very good job! This worked great!!!!

When I pasted the formula from column AU into my sheet, I'm getting a number what appears to be the suppliers book price if its a NON discontinued item.

In your example, the cell shows blank in column AU unless there is a date in the cell...only if i select one of the cells that has an item that has not been discontinued, select copy and paste the values in another cell, I also get a number what is the book price.

Oh. I think I see what you were trying to do in Column AU and there being a hidden number if there is no "disco" displayed.

I think you did this in order to enable the sheet to keep the "book price" should the item become discontinued.

I'd imagine the only way to remove the hidden value from column AU is to then remove the ability to keep the old price. If then the supplier and code is not found in any of the other workbooks, then the value would change to 0.00 for the price.

This works for me if thats what its got to be. Can you post the updated formula to make the disco show the date it was removed without the hidden value?

Let me know :-)

My friend, I think you hit the nail on the head. I'm going to mark this one problem solved and I thank you kindly for your hard work! 10 out of 10!

I pasted the formula into my sheet, but did not see any conditional formatting. Can you tell me where and what cells the conditional formatting its applied to?

Ok!

Thanks again for your effort in solving this one :-)

Can I ask one more question?

If I wanted a conditional formatting that would made an entire row contents red if it contained the word "disco" in column AU

Seems this task must be accomplished by "use a formula to determine which cells to format" under conditional formatting new rules.

I used making the cell range the entire sheet, then formula $AU2="Disco 06/10/2015" and it works...but if I have to for any cell in column AU that has the word "Disco" in it, it would produce the result of highlighting the row in red.

Any idea what formula I could use in Conditional formatting to achieve the highlighted red row if the word "Disco" appears in the column AU?

newdoverman, good job yet again :-)

Thanks!!!