Large replacement

[ritmo2k]

I have a sheet with several thousand unique strings in one column.
On another sheet, I have this same list in one column each with a proposed replacement in the adjacent column.
The order however is not consistent.

Any way within Excel to use sheet 2 as a master set of patterns for a search and replace?

Thanks!

[DGagnon]

could you attach a cample sheet so that we can get a better idea of what you are trying to do?

[ritmo2k]

Sheet 1 below:

A B
1 foo 987987087
2 bar 674564759
3 biz 785654564
4 baz 785685685

Sheet 2 below:

A B
1 foo John
2 bar Jane
3 biz Mary
4 baz Nancy


So, the lists are large, I need to search sheet1-CellA for matches in sheet2-CellA, if one is found, replace Sheet1-CellAn with Sheet2-CellBn.
Although I would love to know how to do this, as the data originated in csv format, I converted Sheet2 data into a sed script with a regex oneliner:

s/foo/John/
s/bar/Jane/
s/biz/Mary/
s/baz/Nancy/

Then applied the file as a sed script against sheet 1 which worked, importing it in to Excel then allowed me to do the math and reporting in Sheet 1 with the required changes.

Thanks!

[DGagnon]

could you attach it as a workbook? its hard to tell where the column breaks are from that data.

[Haseeb Avarakkan]

Try VLOOKUP In Sheet1!C1, then copy down

=IFERROR(VLOOKUP(A1,Sheet2!A:B,2,0),"")

If a cell is blank means Nothing Found for that row. You can copy >> Paste Values over Col_A

[ritmo2k]

Done, thanks!

[DGagnon]

i woudl recomend the same as Haseeb, and use a vlookup in column C then just paset it over A

=IFERROR(VLOOKUP(A1,Sheet2!A:B,2,0),"")

[ritmo2k]

The vlookup worked perfectly.

Thanks!