Need Find ID No. and Replace the Address details of that current ID Number to Shorter Version

[Leicester City Fox]

Need Find ID No.and Replace the Address details of that current ID Number to Shorter Version<o></o>
Hi There<o></o>
Can any body suggest any formula or a ways of looking updata of finding and matching unique IDNo.And replacing the address details below of that ID No.?. I am current usingFind and replace but it taking too long as I have thousands on entries and haveto do many years<o></o>
Source Data Below:<o></o>

A<o></o>​	B<o></o>​	C<o></o>​	D<o></o>​	E<o></o>​
Years<o></o>​	ID no.<o></o>​	Name<o></o>​	Address<o></o>​	charged<o></o>​
2016<o></o>​	123<o></o>​	Mr A<o></o>​	1 Green Road Leicester LE1 123<o></o>​	£1,457.90 <o></o>​
2016<o></o>​	456<o></o>​	Mr B<o></o>​	Blue Close Leicester LE2 123<o></o>​	£456.56 <o></o>​
2016<o></o>​	789<o></o>​	Miss C<o></o>​	2 Yellow Lane Leicester LE3 123<o></o>​	£123.32 <o></o>​
2016<o></o>​	98<o></o>​	Mrs D<o></o>​	3 Red Brick House Leicester LE4 123<o></o>​	£1,234.67 <o></o>​
2016<o></o>​	234<o></o>​	Ms E<o></o>​	4 Orange Patch Leicester LE5 123<o></o>​	£678.00 <o></o>​

<tbody>
</tbody>

<o></o>
Find and Change all addresses that has the following ID No.sto new shorter version below:<o></o>

123<o></o>	1 Green Road <o></o>
789<o></o>	2 Yellow Lane <o></o>
98<o></o>	3 Red Brick House <o></o>
234<o></o>	4 Orange Patch<o></o>

<tbody>
</tbody>

<o></o>
<o></o>
<o></o>
<o></o>
Result that I am looking for is below:<o></o>

A<o></o>​	B<o></o>​	C<o></o>​	D<o></o>​	E<o></o>​
Years<o></o>​	ID no.<o></o>​	Name<o></o>​	Adress<o></o>​	charged<o></o>​
2016<o></o>​	123<o></o>​	Mr A<o></o>​	1 Green Road <o></o>​	£1,457.90 <o></o>​
2016<o></o>​	456<o></o>​	Mr B<o></o>​	Blue Close Leicester LE2 123<o></o>​	£456.56 <o></o>​
2016<o></o>​	789<o></o>​	Miss C<o></o>​	2 Yellow Lane <o></o>​	£123.32 <o></o>​
2016<o></o>​	98<o></o>​	Mrs D<o></o>​	3 Red Brick House <o></o>​	£1,234.67 <o></o>​
2016<o></o>​	234<o></o>​	Ms E<o></o>​	4 Orange Patch<o></o>​	£678.00 <o></o>​

<tbody>
</tbody>

<o></o>
I am current using Find and Replacebut it taking too long as I have thousands on entries and have to do many yearsI don’t know if there a way of using a lookup or match / replace Formula are begreat for any ideas and suggestion that would h<o></o>
Thank you<o></o>
The Leicester City Fox <o></o>
<o></o>

 

[steve the fish]

Using an empty column place something like:

=IFERROR(VLOOKUP(B2,Sheet2!$A$1:$B$4,2,0),D2)

where Sheet2!$A$1:$B$4 houses your lookup table.

You can copy down and then copy and pastespecial over the original addresses

 

[Leicester City Fox]

Hi Steve<o></o>
Good Morning<o></o>
I just want thank you for the formula works a dream you’re astar J<o></o>
Thanks Again<o></o>
Leicester City Fox<o></o>

 

[hiker95]

Leicester City Fox,

Here is a macro solution for you to consider.

You can change the worksheet names in the macro.

Sample raw data worksheets:

Excel 2007
	A	B
1	ID No.
2	123	1 Green Road
3	789	2 Yellow Lane
4	98	3 Red Brick House
5	234	4 Orange Patch
6
Find and Change

Excel 2007
	A	B	C	D	E
1	Years	ID no.	Name	Address	charged
2	2016	123	Mr A	1 Green Road Leicester LE1 123	£1,457.90
3	2016	456	Mr B	Blue Close Leicester LE2 123	£456.56
4	2016	789	Miss C	2 Yellow Lane Leicester LE3 123	£123.32
5	2016	98	Mrs D	3 Red Brick House Leicester LE4 123	£1,234.67
6	2016	234	Ms E	4 Orange Patch Leicester LE5 123	£678
7
Source Data

And, after the macro:

Excel 2007
	A	B	C	D	E
1	Years	ID no.	Name	Address	charged
2	2016	123	Mr A	1 Green Road	£1,457.90
3	2016	456	Mr B	Blue Close Leicester LE2 123	£456.56
4	2016	789	Miss C	2 Yellow Lane	£123.32
5	2016	98	Mrs D	3 Red Brick House	£1,234.67
6	2016	234	Ms E	4 Orange Patch	£678
7
Source Data

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub FindID_Replace()
' hiker95, 11/25/2016, ME977758
Dim wf As Worksheet, ws As Worksheet
Dim c As Range, id As Range
Application.ScreenUpdating = False
Set wf = Sheets("Find and Change")    '<-- you can change the sheet name here
Set ws = Sheets("Source Data")        '<-- you can change the sheet name here
With wf
  For Each c In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    Set id = ws.Columns(2).Find(c.Value, LookAt:=xlWhole)
    If Not id Is Nothing Then
      With ws.Cells(id.Row, 4)
        .Value = c.Offset(, 1).Value
        .Font.Bold = True
      End With
      ws.Cells(id.Row, 2).Font.Bold = True
    End If
  Next c
End With
With ws
  .Columns(4).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the FindID_Replace macro.

 

[Leicester City Fox]

Hi Hiker95<o></o>
Good Morning<o></o>
Thank you for that it’s a different solution to theproblem many thanks J<o></o>
Leicester City Fox<o></o>

 

[hiker95]

Leicester City Fox,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.