data match

[chris.howes]

i have 2 sheets that contain various data they have a a field in common
but they do not match completly

i need to import sheet 1 data to sheet 2

sheet 1 data is in the fomat VI123/45 sheet 2 data is V123

how can i get excel to look at sheet 2 data and import the compleat
number from sheet 1

thnx in advance

 

[ST]

Pl try the following macro.I hope this should solve your
problem.Regards
Sub Macro1()
'
' Macro1
' Macro recorded 2/10/2006 by ST
' If you have 1000 rows in Sheet1 and 200 in Sheet2 then after running
this macro you should
' have 800 rows in sheet 3 which are there in sheet 1 but not in
Sheet2.
' This macro assumes that you have placed common variable in Column A
of both the sheets
'

Dim PrintFlag As Boolean
Dim i As Long, j As Long, k As Long
Dim TempValue1 As String, TempValue2 As String

On Error GoTo Err:

For i = 1 To 1000


TempValue1 =
LTrim(RTrim(ActiveWorkbook.Worksheets("sheet1").Range("A" & i).Value))
For j = 1 To 200
PrintFlag = False
TempValue2 =
LTrim(RTrim(ActiveWorkbook.Worksheets("sheet2").Range("A" & j).Value))
If TempValue1 = TempValue2 Then
Debug.Print "Skipping Row No " & i & " from Sheet1"
Exit For
Else
PrintFlag = True
End If
Next

If PrintFlag = True Then
k = k + 1
Debug.Print "i=" & i & " k= " & k &
ActiveWorkbook.Worksheets("sheet1").Range("F" & i)
ActiveWorkbook.Worksheets("sheet1").Rows(i).Copy
ActiveWorkbook.Worksheets("sheet3").Range("A" & k).Select
ActiveSheet.Paste
End If

Next

Exit Sub

Err:
MsgBox Err.Description, vbOKOnly


End Sub

 

[Dave Peterson]

Maybe a series of =vlookup()'s can be used to bring your data back:

with Vi123 in A2 of Sheet2
and your table in Sheet2 (Vi123/45 somewhere in column A)

=vlookup(a2&"*",sheet2!a:e,5,false)

will bring back the value in column E when