Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Choosing data based on Match to several items | Excel Worksheet Functions | |||
Otto M. - Data Matching | Excel Worksheet Functions | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Shuffling Data To Make Lists Match | Excel Worksheet Functions |