Help with Search and Replace

[lu90001]

Hello ya'll. Please see attached screenshot. I am needing to find the right script for what I am trying to do. Been looking and looking for this one but can't seem to be finding. Your help is and will be greatly appreciated.
I'm trying to find and replace. On column A is my old pricing. Col. B is my identifier a.k.a part no. Col. C is my new pricing I need to replace it with. I need to perform a search and replace, meaning search for the old pricing and replace it with the new one when the identifier is available.

 

[rondeondo]

Hi Lu
You can do a vlookup then replace formula with values?, or xlookup if you're using office 365.

 

[lu90001]

Hi Lu
You can do a vlookup then replace formula with values?, or xlookup if you're using office 365.

I don't know. I think I can since I have the actual Excel downloaded to my computer

 

[lu90001]

I don't know. I think I can since I have the actual Excel downloaded to my computer. I'm a newbie to these Excel VBA, macros, scripts, etc...

 

[rondeondo]

for something like this xlookup is your friend, it's a way of saying to excel, find this value in this list over here and give me the value in the cell in this column on the same row. Xlookup is a replacement for the old vlookup and will work better in your situation.

So first, make the xlookup formula:
column A is your old price and you don't want to keep it. Column C is your new price. Columns B and D are your identifiers as we assume they don't line up 100% accurately.

in a2 put the following:

=XLOOKUP(B2,D:D,C:C,"",0,1)

then copy the formula through the entirity of column B.

breakdown: first argement is the value to find, next is the range to search in, then the range to return. the 0 says make an exact match, the 1 says look from the top of the list.

 

[lu90001]

for something like this xlookup is your friend, it's a way of saying to excel, find this value in this list over here and give me the value in the cell in this column on the same row. Xlookup is a replacement for the old vlookup and will work better in your situation.

So first, make the xlookup formula:
column A is your old price and you don't want to keep it. Column C is your new price. Columns B and D are your identifiers as we assume they don't line up 100% accurately.

in a2 put the following:

=XLOOKUP(B2,D:D,C:C,"",0,1)

then copy the formula through the entirity of column B.

breakdown: first argement is the value to find, next is the range to search in, then the range to return. the 0 says make an exact match, the 1 says look from the top of the list.

Ok got it. But where will the new price be updated? What column?

 

[rondeondo]

You will have the new prices in column a as you requested.
The next step is to copy col a and do paste special... values to take out the formula. Or copy columns a and b to wherever you want your price list

 

[lu90001]

Thank you for your input and dedicated help Rondeondo. What about IF on my new list of sku's that I want to compare, if on the B col is a sku but NOT on col d, I want to be able to add a 0 or another character so I can filter it out and only update on what is able to match up

 

[lu90001]

You will have the new prices in column a as you requested.
The next step is to copy col a and do paste special... values to take out the formula. Or copy columns a and b to wherever you want your price list

It's not quiet working 100%, but i'll be practicing...

 

[rondeondo]

Thank you for your input and dedicated help Rondeondo. What about IF on my new list of sku's that I want to compare, if on the B col is a sku but NOT on col d, I want to be able to add a 0 or another character so I can filter it out and only update on what is able to match up

Hi Lu

if the value is not in the new data you'll get 0, sorry I incorrectly described the arguments for xlookup. I skipped the "" in =XLOOKUP(B2,D:D,C:C,"",0,1). The fourth argument is the result given if the searched value is not found, the "" will give you an empty cell. You can replace that with a 0 or "Not Found" as you require. Then filter for that value. or to exclude that value.