Solved

Match Cell Content, Replace whole Row

Posted on 2011-03-24
5
269 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:pepelepew8
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 250 total points
ID: 35205719
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
 
LVL 1

Author Comment

by:pepelepew8
ID: 35212964
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35213094
I have not inserted any code. If there is code it was from your original file.

Saqib
0
 
LVL 1

Author Comment

by:pepelepew8
ID: 35213663
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35213710
Before I explain, please tell me is it working as desired?
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question