1

I have 2 sheets.

One 'Content' has Product Code in A11-A2912 and Price E11-E2912, and some fields are blank.

The new data is in a separate sheet 'New Price Data' with Product Code in A1-A1409 and Price in B1-B1409. The Product Codes in 'New Price Data' are in numerical order but they aren't in 'Content'.

I need to compare Product Codes and update Price data in 'Content" with data from "New Price Data'

I hope this is clear enough

I know this would of already been answered somewhere but I couldn't find anything I could translate to my problem.

1

Here's how you can update the prices in column E.

  1. Insert a new column F to the Content sheet.
  2. Put this formula in F11, and Fill Down column F to beside the last price in column E (F11:F2912).

=IFNA(VLOOKUP(A11,'New Price Data'!$A$1:$B$1409,2,FALSE),IF(E11=0,"",E11))

  1. Select F11 to the end of the data (F11:F2912), and Copy.
  2. Select E11. Use Paste Special to copy Values.
  3. Delete column F.

For each row in the Content sheet, VLOOKUP checks A1:B1409 in the New Price Data sheet. If VLOOKUP finds the Product Code from column A in the Content sheet, it returns the matching price from the New Price Data sheet. If VLOOKUP can't find the product, it returns #NA.

IFNA looks at the return from VLOOKUP. If the return is not #NA, IFNA returns the VLOOKUP result, otherwise it returns the old price from column E. IF preserves fields that are blank, otherwise column F would be zero where column E is blank.

  • Thank you so much. My data entry speed isn't that fast so this will save me hours over each year. – Ross Thornton Jul 22 '16 at 0:52
0

I could be wrong, but am pretty sure you're going to have to use VBA, unless you want to format the "content" sheet as a table and then sort it in order of product code. Then you can just make the values in the price column of the content sheet equal the values in the price column of the "new price data" sheet.

Yep, I was wrong.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.