On excel how to search & replace a string on a col

问题:

I have this large excel spreadsheet around 10,000 lines and 5,000 unique ids. That I want to like do a search and replace that is based on a data library.

FROM:

TO:

I know this can be done using a fast search and replace on excel, but if the data has around 5,000 unique agent-ids, it can be a daunting task.

Any one have a bright suggestions?

Thanks in advance!

回答1:

Here's some VBA which will find and replace in one sheet based on a list of values in another.

Sub multiFindandReplace()
Dim myList, myRange
Set myList = Sheets("config").Range("A1:B9")
Set myRange = Sheets("Sheet1").Range("B2:I1000")
For Each cel In myList.Columns(1).Cells
myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlPart
Next cel
End Sub

This is searching cells B2:I1000 in sheet1. The config sheet contains 2 columns, the values to look for in column A and if it finds them will replace them with whatever the value is in column B for that row.

回答2:

I was able to make a vb-macro code to do exactly what I need. I may need to adjust some specific cells, but it works for now.

Sub FindAndReplace()
' FindAndReplace Macro
' @author Louie Miranda
' Ability to find the range of ids against another worksheet
' and insert the name on the main sheet
'
    ' Loop over the current worksheet
    For Each c In Worksheets("RECORDS").Range("A3:A7").Cells

        ' Go to Agents sheet
        Sheets("AGENTS").Select

        ' Do a search
        Cells.Find(What:=c, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

        Application.CutCopyMode = False

        ' Choose beside the column to copy
        ActiveCell.Offset(rowOffSet:=0, columnOffset:=-1).Activate
        Selection.Copy

        ' Back to records sheet
        Sheets("RECORDS").Select

        ' Paste on the current row, plus arrange on which row/offset
        Range(c.Address).Offset(0, 1).Select
        ActiveSheet.Paste

    Next c
End Sub