Solved

Find and Replace within 2 columns Excel 2007

Posted on 2011-03-11
20
254 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

747 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now