Solved

Match Cell Content, Replace whole Row

Posted on 2011-03-24
5
271 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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

636 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