data match
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 -- chris.howes ------------------------------------------------------------------------ chris.howes's Profile: http://www.excelforum.com/member.php...fo&userid=5660 View this thread: http://www.excelforum.com/showthread...hreadid=548905 |
data match
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").Ra nge("A" & i).Value)) For j = 1 To 200 PrintFlag = False TempValue2 = LTrim(RTrim(ActiveWorkbook.Worksheets("sheet2").Ra nge("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 chris.howes wrote: 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 -- chris.howes ------------------------------------------------------------------------ chris.howes's Profile: http://www.excelforum.com/member.php...fo&userid=5660 View this thread: http://www.excelforum.com/showthread...hreadid=548905 |
data match
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 "chris.howes" wrote: 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 -- chris.howes ------------------------------------------------------------------------ chris.howes's Profile: http://www.excelforum.com/member.php...fo&userid=5660 View this thread: http://www.excelforum.com/showthread...hreadid=548905 -- Dave Peterson |
All times are GMT +1. The time now is 06:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com