How do I replace specific values in a large file? (Besides using CTRL+F)

[shadestreet]

I have a list of Items in a column with 5000 different entries. Abou
200 of those need to be replaced with a new value that I have in
separate column.

I can't sort the data and match them up because the values are prett
random (all are numeric 10 character codes).

The only thing I could think of was to use CTRL+F to search and replac
the values, but that would be tedious. Any ideas?

Thank

 

[Frank Kabel]

Hi
do you have a logic behind this replacement or a matching table with
old/new value?

 

[AlfD]

Hi!

Help me (and others).

You say you have the replacement values in another column. Can you b
more precise?

Are we looking to take a value from the same row/different column t
replace the value you want to get rid of?

If so, which columns? And are there any values you want to leave alone

 

[shadestreet]

I do have a table that shows the old value in column A and the ne
values in column B.

Im sure there is an easy method, but I am not thinking clearl
today...

thanks for any help you can offer

 

[shadestreet]

Let me give some more info.

The file I want to update lists about 4000 different products. Ther
are about 12 columns that list the attributes of each product. Colum
A is a SKU identifier, basically a numeric code that each product i
classified as. The other columns list things such as "Case weight"
"Cases per Pallet", "Case Height", "Cost of Product", etc.

Now it turns out that about 250+ of these products had their SK
identifier changed, but the actual characteristics left alone.
Therefore, a Videotape that was ID'ed as product 112358 for the first
months of the year was changed to product 314159 for the rest of th
year. When I am trying to analyze the sales of these items, I wa
getting misleading results, and I need to make sure anything that i
listed as more than one product is changed to a single product.

I have in a separate file, the list of each product that has bee
changed, with the old number and the new number side by side. I wan
to use this info and scan through the master file and replace all th
old numbers with the new numbers.

This help

 

[AlfD]

Hi!

Seems tailor made for a lookup, using the list of old and new SKU
table.

Al

 

[shadestreet]

I'm not too experienced using a lookup. I have used VLOOKUP a fe
times, but never to replace data. How can I use lookup to replace th
old product code with the new product codes?

Thank

 

[AlfD]

Hi!

Use it in a spare column.
I'll assume your lookup table is onSheet2, columns A & B with 250
lines.

Check to see if the old SKU is in the table: if not, use the old SKU:
if yes, put the new one in. he following formula does that:

=IF(COUNTIF(Sheet2!$A$1:$A$250,A1)=0,A1,VLOOKUP(A1,Sheet2!$A$1:$B$250,2,FALSE))
in an empty column.

Copy it down.

This should produce the new list in that column.

If you now copy the column, paste special in situ with values checked,
you should end up with a new SKU list ready to copy and paste on top of
the old one.

DON'T DO ANY OF THIS WITHOUT SECURE BACKUP!

Alf