Solved

Find and Replace within 2 columns Excel 2007

Posted on 2011-03-11
20
257 Views
Last Modified: 2012-05-11
I have 2 columns H & J. J has a list of last names. Was wondering if there was a way to search on H&J and put the first name in column H if it matches up with column J.
0
Comment
Question by:ZombieAutopsy
  • 9
  • 8
  • 3
20 Comments
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35113101
Hi,

What values have Column H and what values have Column J? what is the value you want to search and match up?

You said that you want to put the First Name in column H, this first name exist in Column J?
0
 
LVL 8

Author Comment

by:ZombieAutopsy
ID: 35116309
Column J has a list of last names. Like 1700 of then. Most of the are the same just scattered through out J. I need to fill colummn H with the first names that match up with the last names in J.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35116330
Can you upload a sample file?

Sid
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35116357
Ok, if column J has a list of LAST NAMES, from where you are getting the FIRST NAMES you want to match up with column J?
0
 
LVL 8

Author Comment

by:ZombieAutopsy
ID: 35116515
I dont have an actual list of first names. I want to search on a last name and put the first name in front off all the one that match with the last name. I have attached a sample. I am using colums A & B in the files i uploaded. As you see I have Smith in more thatn one row. I need to put John in front of Smith without having to fill in each row manually.
Book1.xlsx
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35116555
Ok, and what name you will put in front of the rest of last names? or you are going to search only for Smith?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35116577
Do you want John in Cell A or in B with the last name?

Sid
0
 
LVL 8

Author Comment

by:ZombieAutopsy
ID: 35116578
No I have to put a name in front of smith. Then i have ti put a first name in front jones, etc. The First name will be the same for all Smiths and Jones, etc..
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35116589
Yes but do you want that in Col A or in Col B with the names?

Sid
0
 
LVL 8

Author Comment

by:ZombieAutopsy
ID: 35116590
In reply to Siddharth, I need John in front off all the cells that have Smith in it.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35116597
I understand that but Do you want John in Cell A or do you want John Smith together in Cell B?

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35116626
This will replace the Smith with John Smith in Col B.

Sub AddNames()
    Dim WS As Worksheet, aCell As Range, sAddress As String
    
    Const sFName As String = "John"
    Const sLName As String = "Smith"
    
    Set WS = ActiveSheet
    Set aCell = WS.Cells.Find(What:=sLName, LookAt:=xlWhole)
    sAddress = aCell.Address
    If Not aCell Is Nothing Then
        On Error GoTo Ext
        Do
            aCell.Value = sFName & " " & sLName
            Set aCell = WS.Cells.FindNext(aCell)
        Loop While Not aCell Is Nothing And aCell.Address <> sAddress
    End If
Ext:
End Sub

Open in new window


Sample Attached

Run the Sub AddNames()

Sid
Sample.xls
0
 
LVL 8

Author Comment

by:ZombieAutopsy
ID: 35116632
Thanks Sid, but i need the John to appear in Column A. Column B needs to keep Smith only in it..
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35116688
That's what I have been asking you all this time :)

Anyways, peplace the above code with

Sub AddNames()
    Dim WS As Worksheet, aCell As Range, sAddress As String
    
    Const sFName As String = "John"
    Const sLName As String = "Smith"
    
    Set WS = ActiveSheet
    Set aCell = WS.Cells.Find(What:=sLName, LookAt:=xlWhole)
    sAddress = aCell.Address
    If Not aCell Is Nothing Then
        On Error GoTo Ext
        Do
            aCell.Offset(, -1).Value = sFName
            Set aCell = WS.Cells.FindNext(aCell)
        Loop While Not aCell Is Nothing And aCell.Address <> sAddress
    End If
Ext:
End Sub

Open in new window


Now Try it.

Sid
0
 
LVL 8

Author Comment

by:ZombieAutopsy
ID: 35116814
I explained it 3 times too. i will give this a try and let you know.
0
 
LVL 8

Author Comment

by:ZombieAutopsy
ID: 35116905
I have to do this in Visual basic right? doesnt seem to be working....
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35116911
You have to paste this in a module. And from the relevant sheet, run the macro "AddNames"

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35116958
I just noticed that if the "Applies To" Range is the same then it will give you both the formulas else it will give the 1st one twice.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35116980
Sorry, the last post is in the wrong thread. Wanted to paste in some other thread.

Sid
0
 
LVL 8

Author Comment

by:ZombieAutopsy
ID: 35117454
Perfect, made my life so much easier. I might have a abnother question doing the same thing with numbers. If i cant figure it out i will post a new question.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

789 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