CODEWITHSUNDEEP
excelrename
Graeme
Graeme

Reputation: 21

Renaming Cells In Excel

I don't know if this is possible, but I have been advised to post here and the most likely place for an answer.

I have four columns in excel as follows

A: all product id's
B: reference No.
C: selected product id's
D: product codes.

What I would like excel to do is search column A for the id's in column C, and if there is a match, rename it to the corresponding row in column D from the search in column D. I can't use vlookup easily as the product id's in column A may repeat upto 8 times.

I hope that makes sense and I will try to answer all questions that I can. I know nothing about excel, beyond on the basics so if anyone could shed some light on this I would be grateful.

Upvotes: 2

Views: 2710

Answers (3)

Dan
Dan

Reputation: 45752

Try this:

Insert a new column before A. So now all your columns have shifted up one, i.e. your A is now B etc.

Now in cell A1 (the newly inserted A1, not your original A1 which is now B1)

=VLOOKUP(B1, $D$1:$E$1000, 2, 0)

where 1000 is whatever your last row is. And drag the formula down.

You haven't specified what happens if an ID is in col A but not in col C. If you want it to just stay what it was in Col A then use this formula instead:

=IF(ISNA(VLOOKUP(B1, $D$1:$E$1000, 2, 0)), B1, VLOOKUP(B1, $D$1:$E$1000, 2, 0))

Upvotes: 1

BdR
BdR

Reputation: 3048

So your data is something like this? I don't understand what your expected result is?

A  B    C  D    expected result
1  101  1  201  ?
1  102  4  204
2  103
3  104
3  105
4  106

Upvotes: 0

CustomX
CustomX

Reputation: 10113

This should do the trick:

Sub test()

LastRowA = Cells(Rows.Count, "A").End(xlUp).Row
LastRowC = Cells(Rows.Count, "C").End(xlUp).Row

For i = 1 To LastRowA
    For j = 1 To LastRowC
        If Range("A" & i).Value = Range("C" & j).Value Then
            Range("D" & i).Value = Range("C" & j).Address(False, False)
        End If
    Next j
Next i

End Sub

Upvotes: 0

Related Questions