Calculate with Vlookup or Macro?

JadonR

Board Regular
Joined
Apr 15, 2009
Messages
126
I would like to raise the price of certain items by the amount found on Sheet2 in Column C. I would like all prices that don't match these UPC codes to remain unchanged and I would also prefer to have the new changed numbers to appear in there original Column C on Sheet1. Intended Results can be seen on Sheet1. Not sure if I should use the Vlookup function or a macro, or maybe there is even a better solution.

Spreadsheet may be viewed/edited at http://spreadsheets.google.com/ccc?key=pT0dg5ZEHWLMyhNJLiPLTfA.

Also, both sheets can be seen below:



 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

MisterCrash

Active Member
Joined
Jan 27, 2003
Messages
279
If you don't care about preserving the original prices of these items, it would probably be easiest to do the following:

In cell D2, enter the following formula, and copy it down column D:

=C2+IF(COUNTIF(Sheet2!$A$2:$A$4, A2)=0, 0, VLOOKUP(A2, Sheet2!$A$2:$C$4, 3, FALSE))

Once you are satisfied that these numbers are what you intend to charge, copy column D and Paste Special | Values into column C. You can then delete the entries in column D.
 

JadonR

Board Regular
Joined
Apr 15, 2009
Messages
126
Thanks MisterCrash for the quick reply. That does work, but is there a macro or script I can run so I can avoid the copy and paste special? On a short list this wouldn't be bad, but I have over 20,000 lines that I will be running this on regularly. So I would definitely prefer to preserve the original column if at all possible.
 

MisterCrash

Active Member
Joined
Jan 27, 2003
Messages
279
With that many rows, I would probably recommend that you keep a table on Sheet2 that lists all the UPC codes along with their prices, and use a VLOOKUP function on Sheet1 to find the current price.

If you want to keep track of how the prices have changed over time, you can keep the original price in column C of Sheet2, then put the incremental changes in columns D, E, F, and so on. You can then have a column that has the final price (equal to the sum of all of the prices in that row). You can reference this column with your VLOOKUP on Sheet1 to return the final price.
 

JadonR

Board Regular
Joined
Apr 15, 2009
Messages
126
Thanks, I do appreciate the advice. However, I import a price list every week, and hence the prices change that often. Keeping track of how often the prices change is unnecessary for me, and since the prices change so often, I was hoping that this would take the extra work out of it for me. I'm looking for a way to preserve the original column, rather the copying and pasting. Thanks!
 

JadonR

Board Regular
Joined
Apr 15, 2009
Messages
126
Re: Calculate with Vlookup or Macro? (with pics)

Also, would it be possible to make this formula to subtract, divide, or multiply as well as add? The best option would be to have "drop down" box to choose from. (See illustration below)


Sheet2new.jpg

 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,621
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top