Hello Gurus,
Working in Excel 2007.
I'm attempting to compare 2 lists located on separate sheets of the same workbook. The comparison should be based on columns A B & C on both sheets with one twist, from this comparison I would like to generate a new list, to be located on a third sheet, of items that are matching except for the Version code (Column D). However, I only want to see results for lines where the version code is later in the alphabet than the similar item(s) in List 2. For example....
Sheet 1 List 1
[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl66, width: 64"]Year[/TD]
[TD="class: xl66, width: 64"]Model ID[/TD]
[TD="class: xl66, width: 64"] Code[/TD]
[TD="class: xl66, width: 64"]Version[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2018[/TD]
[TD="class: xl65, align: right"]1201[/TD]
[TD="class: xl66"] 2T[/TD]
[TD="class: xl66"]C[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2018[/TD]
[TD="class: xl65, align: right"]1201[/TD]
[TD="class: xl66"] 3P[/TD]
[TD="class: xl66"]A[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2018[/TD]
[TD="class: xl65, align: right"]1201[/TD]
[TD="class: xl66"] 3P[/TD]
[TD="class: xl66"]B[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2018[/TD]
[TD="class: xl65, align: right"]1201[/TD]
[TD="class: xl66"] 3Z[/TD]
[TD="class: xl66"]A[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2018[/TD]
[TD="class: xl65, align: right"]1201[/TD]
[TD="class: xl66"] 63[/TD]
[TD="class: xl66"]A[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2 List 2
[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Year[/TD]
[TD="class: xl65, width: 64"]Model ID[/TD]
[TD="class: xl65, width: 64"]Code[/TD]
[TD="class: xl65, width: 64"]Version[/TD]
[/TR]
[TR]
[TD="class: xl65"]2018[/TD]
[TD="class: xl65"]1201[/TD]
[TD="class: xl65"]GY[/TD]
[TD="class: xl65"]A[/TD]
[/TR]
[TR]
[TD="class: xl65"]2018[/TD]
[TD="class: xl65"]1201[/TD]
[TD="class: xl65"]H6[/TD]
[TD="class: xl65"]A[/TD]
[/TR]
[TR]
[TD="class: xl65"]2018[/TD]
[TD="class: xl65"]1201[/TD]
[TD="class: xl65"]2T[/TD]
[TD="class: xl65"]A[/TD]
[/TR]
[TR]
[TD="class: xl65"]2018[/TD]
[TD="class: xl65"]1201[/TD]
[TD="class: xl65"]2T[/TD]
[TD="class: xl65"]B[/TD]
[/TR]
[TR]
[TD="class: xl65"]2018[/TD]
[TD="class: xl65"]1201[/TD]
[TD="class: xl65"]H9[/TD]
[TD="class: xl65"]A[/TD]
[/TR]
[TR]
[TD="class: xl65"]2018[/TD]
[TD="class: xl65"]1201[/TD]
[TD="class: xl65"]63[/TD]
[TD="class: xl65"]B[/TD]
[/TR]
[TR]
[TD="class: xl65"]2018[/TD]
[TD="class: xl65"]1201[/TD]
[TD="class: xl65"]H4[/TD]
[TD="class: xl65"]A[/TD]
[/TR]
</tbody>[/TABLE]
The only item I expect to see on sheet3 would be the first row (2018 1201 2T C) from list 1. This is because it is the only code where the first 3 columns have a match but the Version on list 1 is later in the alphabet than the latest on list 2 (C vs A & B). The items in red do not qualify since List 1 has a version code that is earlier in the alphabet than the same item on list 2.
The version code will always be alphabetical. There can be a variable number of matches as shown in the example above.
Also, my lists are not formatted the same so I have some preliminary code to "clean" them up a bit. It would be preferable to include this in any solution.
Thank you in advance for your help.
Working in Excel 2007.
I'm attempting to compare 2 lists located on separate sheets of the same workbook. The comparison should be based on columns A B & C on both sheets with one twist, from this comparison I would like to generate a new list, to be located on a third sheet, of items that are matching except for the Version code (Column D). However, I only want to see results for lines where the version code is later in the alphabet than the similar item(s) in List 2. For example....
Sheet 1 List 1
[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl66, width: 64"]Year[/TD]
[TD="class: xl66, width: 64"]Model ID[/TD]
[TD="class: xl66, width: 64"] Code[/TD]
[TD="class: xl66, width: 64"]Version[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2018[/TD]
[TD="class: xl65, align: right"]1201[/TD]
[TD="class: xl66"] 2T[/TD]
[TD="class: xl66"]C[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2018[/TD]
[TD="class: xl65, align: right"]1201[/TD]
[TD="class: xl66"] 3P[/TD]
[TD="class: xl66"]A[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2018[/TD]
[TD="class: xl65, align: right"]1201[/TD]
[TD="class: xl66"] 3P[/TD]
[TD="class: xl66"]B[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2018[/TD]
[TD="class: xl65, align: right"]1201[/TD]
[TD="class: xl66"] 3Z[/TD]
[TD="class: xl66"]A[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2018[/TD]
[TD="class: xl65, align: right"]1201[/TD]
[TD="class: xl66"] 63[/TD]
[TD="class: xl66"]A[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2 List 2
[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Year[/TD]
[TD="class: xl65, width: 64"]Model ID[/TD]
[TD="class: xl65, width: 64"]Code[/TD]
[TD="class: xl65, width: 64"]Version[/TD]
[/TR]
[TR]
[TD="class: xl65"]2018[/TD]
[TD="class: xl65"]1201[/TD]
[TD="class: xl65"]GY[/TD]
[TD="class: xl65"]A[/TD]
[/TR]
[TR]
[TD="class: xl65"]2018[/TD]
[TD="class: xl65"]1201[/TD]
[TD="class: xl65"]H6[/TD]
[TD="class: xl65"]A[/TD]
[/TR]
[TR]
[TD="class: xl65"]2018[/TD]
[TD="class: xl65"]1201[/TD]
[TD="class: xl65"]2T[/TD]
[TD="class: xl65"]A[/TD]
[/TR]
[TR]
[TD="class: xl65"]2018[/TD]
[TD="class: xl65"]1201[/TD]
[TD="class: xl65"]2T[/TD]
[TD="class: xl65"]B[/TD]
[/TR]
[TR]
[TD="class: xl65"]2018[/TD]
[TD="class: xl65"]1201[/TD]
[TD="class: xl65"]H9[/TD]
[TD="class: xl65"]A[/TD]
[/TR]
[TR]
[TD="class: xl65"]2018[/TD]
[TD="class: xl65"]1201[/TD]
[TD="class: xl65"]63[/TD]
[TD="class: xl65"]B[/TD]
[/TR]
[TR]
[TD="class: xl65"]2018[/TD]
[TD="class: xl65"]1201[/TD]
[TD="class: xl65"]H4[/TD]
[TD="class: xl65"]A[/TD]
[/TR]
</tbody>[/TABLE]
The only item I expect to see on sheet3 would be the first row (2018 1201 2T C) from list 1. This is because it is the only code where the first 3 columns have a match but the Version on list 1 is later in the alphabet than the latest on list 2 (C vs A & B). The items in red do not qualify since List 1 has a version code that is earlier in the alphabet than the same item on list 2.
The version code will always be alphabetical. There can be a variable number of matches as shown in the example above.
Also, my lists are not formatted the same so I have some preliminary code to "clean" them up a bit. It would be preferable to include this in any solution.
Code:
Sub format()
Dim lastrow1 As Long
Dim lastrow2 As Long
lastrow1 = Sheets("Sheet1").Range("A" & Rows.count).End(xlUp).Row
lastrow2 = Sheets("Sheet2").Range("A" & Rows.count).End(xlUp).Row
Sheets("Sheet2").Select
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("D2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(2, 1), Array(4, 1)), TrailingMinusNumbers:= _
True
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("D1") = "Version"
End Sub
Thank you in advance for your help.