Solved

Find and Replace within 2 columns Excel 2007

Posted on 2011-03-11
20
256 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
.Range Set 25 76
Concatenate  strings with original parameters 21 41
Excel format formula for currency 15 22
Clear Filter 8 37
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

770 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