| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Value Replacement
Hello, need help please. I have a tons of data in excel that I want to change
the data in a certain column. Heres 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Value Replacement
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 "palups" wrote in message ... Hello, need help please. I have a tons of data in excel that I want to change the data in a certain column. Heres 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Value Replacement
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 --------------- Jacob Skaria "palups" wrote: Hello, need help please. I have a tons of data in excel that I want to change the data in a certain column. Heres 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Value Replacement
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 "Ashish Mathur" wrote in message ... 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 "palups" wrote in message ... Hello, need help please. I have a tons of data in excel that I want to change the data in a certain column. Heres 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Value Replacement
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$1 000,",",)," ",),0))) If this post helps click Yes --------------- Jacob Skaria "palups" wrote: Hello, need help please. I have a tons of data in excel that I want to change the data in a certain column. Heres 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Value Replacement
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. "Ashish Mathur" wrote: 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 "palups" wrote in message ... Hello, need help please. I have a tons of data in excel that I want to change the data in a certain column. Heres 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Value Replacement
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. "Jacob Skaria" wrote: 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$1 000,",",)," ",),0))) If this post helps click Yes --------------- Jacob Skaria "palups" wrote: Hello, need help please. I have a tons of data in excel that I want to change the data in a certain column. Heres 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| number replacement | Excel Discussion (Misc queries) | |||
| Replacement for sumproduct? | Excel Discussion (Misc queries) | |||
| #N/A Replacement | Excel Discussion (Misc queries) | |||
| Replacement | Excel Discussion (Misc queries) | |||
| Binder replacement for XP | Excel Discussion (Misc queries) | |||
Linear Mode
