Value Replacement

P

palups

Hello, need help please. I have a tons of data in excel that I want to change
the data in a certain column. Here’s the scenario, supposed you have a two
worksheet SHEET 1 and 2. Data in Sheet 1 is the old one that we have to
replace by the data in sheet 2, but only the Col B in Sheet 1. Formula should
replace the sheet 1 ColB with the sheet 2 ColA which have the same value in
sheet 1 Col C,D and sheet 2 ColB. Thank you.

SHEET 1

A…….....B………...C................D

21345 203 Manuel Cotto
65746 123 Juan Marquez
78695 003 Floyd Mayweather
65748 876 Ricky Hatton
65746 123 John Marquez
65748 876 Timmy Hatton
65746 123 Tom Marquez
21345 203 Ricky Cotto
21345 203 Jaun Cotto
65746 123 Archy Marquez
21345 203 Pedro Cotto


SHEET 2

A………...........B
3452 Manuel Cotto
2000 Juan, Marquez
1982 Floyd Mayweather
0564 Timmy, Hatton


palups
 
A

Ashish Mathur

Hi,

In cell E2 of sheet2, use the following formula and copy down

=index(sheet2!$A$2:$B$5,match($c2&$D2,sheet2!$B$2:$B$5,0),1)

Now Copy all the values in column E and paste them as values. You may now
cut and paste column E on column B

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
J

Jacob Skaria

Try the below formula in a temporary column say ColE...

=IF(ISNA(MATCH(C1& " " & D1,Sheet2!B:B,0)),IF(ISNA(
MATCH(C1& ", " & D1,Sheet2!B:B,0)),"",
INDEX(Sheet2!A:A,MATCH(C1& ", " & D1,Sheet2!B:B,0))),
INDEX(Sheet2!A:A,MATCH(C1& " " & D1,Sheet2!B:B,0)))

If this post helps click Yes
 
A

Ashish Mathur

Hi,

For go to mention. Kindly highlight all the entries in column B of sheet 2
and find/replace all , with <nothing>

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
J

Jacob Skaria

Another solution..Please note that this is an array formula. You create array
formulas in the same way that you create other formulas, except you press
CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can
notice the curly braces at both ends like "{=<formula>}"

Try this in Sheet1 B1 or in E1 and copy down as required

=IF(ISNA(MATCH(C1&D1,SUBSTITUTE(SUBSTITUTE(
Sheet2!$B$1:$B$1000,",",)," ",),0)),"",INDEX(Sheet2!$A$1:$A$1000,
MATCH(C1&D1,SUBSTITUTE(SUBSTITUTE(Sheet2!$B$1:$B$1000,",",)," ",),0)))

If this post helps click Yes
 
P

palups

Hello Ashish and Jacob,

Thanks for the reply. Sorry it doesn't work or maybe I just didn't follow
your instruction, anyway just want to clear that my output should be like this

A……........B………......C.............D

21345 3452 Manuel Cotto
65746 2000 Juan Marquez
78695 1982 Floyd Mayweather
65748 0564 Ricky Hatton
65746 2000 John Marquez
65748 0564 Timmy Hatton
65746 2000 Tom Marquez
21345 3452 Ricky Cotto
21345 3452 Jaun Cotto
65746 2000 Archy Marquez
21345 3452 Pedro Cotto

Sheet1 ColB Replaces all the value from Sheet2 ColA Value with just the
reference of Sheet 2 ColB and Sheet1 Col C or D. You will noticed that in my
previous post Sheet1 ColB there are 4 entry with a value of "123" and ColD
value of "Marquez". The Formula should replace all the "123" with "2000".
"2000" is from Sheet2 ColA with ColB value of "Marquez" is it possible?

Thank you.
 
P

palups

Hello Ashish and Jacob,

Thanks for the reply. Sorry it doesn't work or maybe I just didn't follow
your instruction, anyway just want to clear that my output should be like this

A……........B………......C.............D

21345 3452 Manuel Cotto
65746 2000 Juan Marquez
78695 1982 Floyd Mayweather
65748 0564 Ricky Hatton
65746 2000 John Marquez
65748 0564 Timmy Hatton
65746 2000 Tom Marquez
21345 3452 Ricky Cotto
21345 3452 Jaun Cotto
65746 2000 Archy Marquez
21345 3452 Pedro Cotto

Sheet1 ColB Replaces all the value from Sheet2 ColA Value with just the
reference of Sheet 2 ColB and Sheet1 Col C or D. You will noticed that in my
previous post Sheet1 ColB there are 4 entry with a value of "123" and ColD
value of "Marquez". The Formula should replace all the "123" with "2000".
"2000" is from Sheet2 ColA with ColB value of "Marquez" is it possible?

Thank you.
 
Top