Automate Excel: Update list with new values
Overview
Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price list with new values.
Sheet 1 is the old price list. It contains 5000 products and amounts.
Sheet2 is the new price list. It contains 2000 random products from the old price list with new prices. (There are no new products)
How to create a new list with the latest prices.
Copy all products from sheet1 into sheet 3
Now let us find out if a new price exists.
Sheet3, formula in B2:
Double press with left mouse button on lower right corner of cell B2.
The formula is copied down to last adjacent product.
Sheet3, formula in C2:
Double press with left mouse button on lower right corner of cell C2 to copy the formula as far down as needed.
I created column B to make sure the values are the same as in sheet2 and to make it easier to understand the formula in cell C2.
Explaining the formula in cell C2
Step 1 - Find out if a new price exists
=IF(ISERROR(MATCH(A2, Sheet2!$A$2:$A$2001, 0)), INDEX(Sheet1!$B$2:$B$5000, MATCH(A2, Sheet1!$A$2:$A$5000, 0)), INDEX(Sheet2!$B$2:$B$2001, MATCH(A2, Sheet2!$A$2:$A$2001, 0)))
Match returns the relative position of an item in (Sheet2!$A$2:$A$2001) that matches a specified value (A2).
MATCH(A2, Sheet2!$A$2:$A$2001, 0) returns #N/A. This means "Product AT" can´t be found in sheet2.
ISERROR(MATCH(A2, Sheet2!$A$2:$A$2001, 0)) returns TRUE.
Step 2 - Identify sheet and price
The formula returned TRUE in cell C2. This means there is no new price. Let´s find the old price in sheet1 instead.
=IF(ISERROR(MATCH(A2, Sheet2!$A$2:$A$2001, 0)), Formula if TRUE, Formula if FALSE)
=IF(ISERROR(MATCH(A2, Sheet2!$A$2:$A$2001, 0)), INDEX(Sheet1!$B$2:$B$5000, MATCH(A2, Sheet1!$A$2:$A$5000, 0)), INDEX(Sheet2!$B$2:$B$2001, MATCH(A2, Sheet2!$A$2:$A$2001, 0)))
INDEX(Sheet1!$B$2:$B$5000, MATCH(A2, Sheet1!$A$2:$A$5000, 0))
MATCH(A2, Sheet1!$A$2:$A$5000, 0) returns 1. "Product AT" is found on the first row on sheet1.
INDEX(Sheet1!$B$2:$B$5000, 1) returns $43,90.
If formula in step 1 had returned False
If the formula had returned False in cell C2, we would need to look for the new price in sheet2.
=IF(ISERROR(MATCH(A2, Sheet2!$A$2:$A$2001, 0)), Formula if TRUE, Formula if FALSE)
=IF(ISERROR(MATCH(A2, Sheet2!$A$2:$A$2001, 0)), INDEX(Sheet1!$B$2:$B$5000, MATCH(A2, Sheet1!$A$2:$A$5000, 0)), INDEX(Sheet2!$B$2:$B$2001, MATCH(A2, Sheet2!$A$2:$A$2001, 0)))
INDEX(Sheet2!$B$2:$B$2001, MATCH(A2, Sheet2!$A$2:$A$2001, 0))
Get excel example file
automate_excel_pricelist.zip (300 KB)
Weekly Blog EMAIL
[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Extract unique distinct records based on a criterion
In a previous article "Automatically filter unique row records from multiple columns", I presented a solution to filter out unique […]
This article describes how to create a random playlist based on a given number of teams using an array formula. […]
Reverse a list ignoring blanks
This article demonstrates two different formulas, one for Excel 365 and one for earlier versions. Table of Contents Reverse a […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
Learn how to return values based on where you enter the UDF
In this vba tutorial I am going to show you how to return values from an udf, depending on where […]
Count unique distinct numbers across multiple sheets
The image above demonstrates three different formulas in column E that counts unique numbers unique distinct numbers duplicate numbers from […]
Table of Contents Extract shared values between two columns Extract shared values between two columns - Excel 365 Extract shared […]
Array formula in B15: =INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0)) Copy cell B15 and paste it to […]
Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]
5 Responses to “Automate Excel: Update list with new values”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi,
Using the IFERROR in Excel 2007, you could use the formula below, whereby you do not need the Helper column B.
=IFERROR(VLOOKUP(A2, Sheet2!$A$2:$B$2001, 2, 0), VLOOKUP(Sheet3!A2, Sheet1!$A$2:$B$5000, 2, 0))
Why is it necessary to make the Formula an Array formula, it seems to work just fine witout it
Kanti Chiba,
you are right. It is not necessary to create an array formula.
Column B is not a helper column, it is created to make the formula in column C easier to understand.
Thanks for your contribution!
if you have a price list for products and you want to update that from a new price list the below formula may help
=IFERROR(INDEX($H$2:$I$4, MATCH(A2, $H$2:$H$4,0), 2),B2)
Hi, what if there is some new products in the new price list. And what I want to do is to identify the "values" of these product, so I know the codes of the new products. Can we do that?
1. Create one column and do a VLOOKUP for your price in your 'new price sheet;
2. create a second column with the code: 'IFNA'
if your price sheet has #NA select the cell with new price, if it has a value from the old sheet basically its NOT #n/a ; and Excel will keep the old price.
suppose you have an old price in the Cell?
you can create a third column, use your new price sheet and turn all prices in your master sheet to the text '#N/A' then do steps 1 & 2.
Granted this is a 3 column, 3 step process,
But the time and grief saved creating a single formula solution that actually works in the end is priceless.
and you can create a Macro to do this.