i want to find and replace by column

[itsmebestz]

Thank you sir

 

[hiker95]

itsmebestz,

Welcome to the MrExcel forum.

The following screenshots and macro are based on your posted picture/graphic.

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G
1
2
3	50	61				50	1.9
4	63	72				61	0
5	69	74				63	1.8
6						69	1.96
7						72	2.1
8						74	2.5
9
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G
1
2
3	1.9	0				50	1.9
4	1.8	2.1				61	0
5	1.96	2.5				63	1.8
6						69	1.96
7						72	2.1
8						74	2.5
9
Sheet1

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.

Option Explicit
Sub FindAndReplace()
' hiker95, 08/26/2013
' http://www.mrexcel.com/forum/excel-questions/722587-i-want-find-replace-column.html
Dim a As Variant, f As Variant
Dim i As Long, ii As Long
a = Cells(3, 1).CurrentRegion
f = Cells(3, 6).CurrentRegion
For i = 1 To UBound(a, 1)
  For ii = 1 To UBound(f, 1)
    If a(i, 1) = f(ii, 1) Then
      a(i, 1) = f(ii, 2)
    End If
    If a(i, 2) = f(ii, 1) Then
      a(i, 2) = f(ii, 2)
    End If
  Next ii
Next i
Cells(3, 1).CurrentRegion = a
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

Then run the FindAndReplace macro.