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!
LVL 1
pepelepew8Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Try this file. I have used formulas instead of VBA. You will have to select the first four lines and copy down by dragging.
Lookup-dataset-on-keyword.xlsx
0
 
pepelepew8Author Commented:
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
0
 
Saqib Husain, SyedEngineerCommented:
I have not inserted any code. If there is code it was from your original file.

Saqib
0
 
pepelepew8Author Commented:
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
0
 
Saqib Husain, SyedEngineerCommented:
Before I explain, please tell me is it working as desired?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.