0

Imagine the list being a lot longer... Is there a way to replace the cells in both A & B with the appropriate name/ID from Column C? "Appropriate Name" is whats in column C (match names). This is final result YES COPY/PASTE WORKS, but when this whole thing is 5000 long and column C isn't in perfect order, it takes many hours.

enter image description here

Improve this question
3
  • I made up the names quickly because my actual list of names is about 1000 rows and that's why copy/paste or find/replace will take too long.
    – Jason
    Oct 15, 2015 at 4:13
  • 1
    What is an "appropriate name"? Please edit and give an example of what the table looks like after the desired changes).
    – DavidPostill ♦
    Oct 15, 2015 at 7:41
  • 1
    Highlight A and B, rightclick, delete... I am really curious as to why copy and paste can't be used...
    – Burgi
    Oct 15, 2015 at 9:23

1 Answer 1

Reset to default
0

Using vba you can substitute the looked up value this way Adjust the range to point where your substitute values are and the Range of the items you are looking up

Sub substituteLookedUp()
Dim myStr As String
Dim matchDbl As Double
Dim Rng As Range
   Set Rng = Range("C1:C10")

   For Each cell In Range("a1:b12")
      If cell <> "" Then
           myStr = Mid(cell, WorksheetFunction.Find(" ", cell) + 1, Len(cell) - WorksheetFunction.Find(" ", cell)) & "_" & Left(cell, WorksheetFunction.Find(" ", cell) - 1) & "*"
           matchDbl = WorksheetFunction.Match(myStr, Rng, 0)
           newStr = Range("C" & matchDbl)
         Else: newStr = ""
       End If
     cell.Value = newStr
   Next

End Sub

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .