Link to home
Start Free TrialLog in
Avatar of pepelepew8
pepelepew8

asked on

Match Cell Content, Replace whole Row

Hi Experts,

I have an situation, which is like this

Sheet 1
 Row1        Col 1    Col 2        Col3, etc
 Row2        1          Apple       nnn
 Row3        some information
 Row4
 Row5        2          Banana    mmm
 Row6        some information


Sheet 2
 Row1        Col 1    Col 2        Col3, etc
 Row2        1          Papaya     xxx
 Row3        some information
 Row4        papaya@farm.com
 Row5
 Row6        2          Banana    yyy
 Row7        some information
 Row8        Banana@farm.com

i would love a way to match a Cell in Col2 of both sheets, once match like Banana in Sheet 1 and Sheet 2, whole Row6 of Sheet2 will be replaced with Row5 of Sheet 1. the outcome is as below (note that col3 the "description" of Banana being replaced)

 Sheet 2
 Row1        Col 1    Col 2        Col3, etc
 Row2        1          Papaya     xxx
 Row3        some information
 Row4        papaya@farm.com
 Row5
 Row6        2          Banana    mmm
 Row7        some information
 Row8        Banana@farm.com

i will need a script on this, to match more than 1000 accounts.

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pepelepew8
pepelepew8

ASKER

Hi ssaqibh,

i had downloaded the file and try to use it, but it seems im confused with the code, can please help explaining the code?

thanks so that i am also able to customize it

cheers =D
I have not inserted any code. If there is code it was from your original file.

Saqib
Hi Saqib,

sorry for didnt make it clear, i mean this formula

=IF(ISNA(MATCH(B6,Sheet1!B:B,0)),Sheet2!C6,INDEX(Sheet1!$A$1:$C$10000,MATCH(B6,Sheet1!B:B,0),3))

can help explaining abit? thanks =D
Before I explain, please tell me is it working as desired?