Need help with "compare, find, copy adjacent, replace value" formula

[nuuube]

I'm in desperate need of help for a formula in xls that can do the following. Suppose I have two price lists containing part numbers (column A) and prices (column B). Price list X and Price list Y. Price list X has say 500 rows (part numbers which may be repeated) and prices for each part number. This is our existing price list file.

Price list Y has 750 rows (part numbers may be repeated also) and prices for each part. I need a formula/macro that can read say first row (after the column titles) in Column A on price list X, go to price list Y, check for that exact match through the column A, when found, copy the value of column B on the same row and replace the value of Column B for the value copied on price list X, for that part number (in this case is first row).

Sounds a bit confusing. I am trying to update existing price list. List contains part number and prices. Original list has limited part numbers. New list with new pricing has more part numbers. I need to update prices in existing/original price list that are on the new price list. Both price lists have different amount of part numbers and are sorted differently.

Currently, I have to do it all manually. I have to: go to list X > take the first row part number > copy it > go to new price list "List Y" > CRTL + FIND > Paste Clipboard > Go to Column B (price) for the matching part # > copy it > go back to list X > go to column B (for the same part #) > paste;

Now imagine if there are 4000 part numbers. Row by row, takes forever. Feels like I'm back in the stoneage.

Someone please help!

 

[Mindpsyche]

See if this link helps:

http://www.cpearson.com/Excel/FlexLookup.aspx


Edit: On second thoughts check this link:


http://www.contextures.com/xlFunctions02.html

 

[TomPC]

Hi Nuuube

I'm sure I can help but let's clarify a couple of things.

1. Your price list has two columns: Part Number and Price. Am I right in saying that some of the Part Numbers appear more than once? That seems strange. It's like saying: Smarties 50p... Smarties 40p. What do you want to do when a part number appears twice? Will the price always be the same?

2. You talk about replacing values in List X with values in List Y. Do they need to be replaced (you'll need a macro) or can we just create a new updated list in a separate sheet or whatever. Or add a new column each day with "Latest Prices (date)" in the header?

3. There are 500 rows in X and 750 in Y. What do you want to do if a part number appears in Y but does not appear in X. Add it to X or ignore it?

4. Am I right in summarising this requirement as being a price list X which you want to update every time a newer price list Y comes in?

Tom

 

[nuuube]

Hi Nuuube

I'm sure I can help but let's clarify a couple of things.

1. Your price list has two columns: Part Number and Price. Am I right in saying that some of the Part Numbers appear more than once? That seems strange. It's like saying: Smarties 50p... Smarties 40p. What do you want to do when a part number appears twice? Will the price always be the same?

2. You talk about replacing values in List X with values in List Y. Do they need to be replaced (you'll need a macro) or can we just create a new updated list in a separate sheet or whatever. Or add a new column each day with "Latest Prices (date)" in the header?

3. There are 500 rows in X and 750 in Y. What do you want to do if a part number appears in Y but does not appear in X. Add it to X or ignore it?

4. Am I right in summarising this requirement as being a price list X which you want to update every time a newer price list Y comes in?

Tom

Hello,

Thank you for your quick response. Here you go:

1.) Yes, some of the part numbers appear more than once in column A, but the price is also going to be the same/matching. For example, if part number is is "111111" and price is "100", every time you'll find "111111" on price list X, price will always have to be "100", so the format is still consistent. It may "appear" more than once on the price list, but the values on both columns (a & b) will match, always.

2.) The new prices can be imported from price list Y to price list X to a new column. I can always just delete the original "price" column and rename the new one to the old name. As long as the price value is correct for the corresponding part #, from list Y.

3.) If list Y has part numbers (column A) that are not on list X, it would be a good idea to "highlight" them on list Y, so that we can go back and add the missing part numbers. But we wouldn't transfer them to list X or anything.

4.) Correct. If we have 5000 parts, and every month the our vendor adjusts their pricing, this way, we wouldn't have to sit through each part #, validate the price change and update if necessary.

I thank you again for looking into this for me.