Selected Answer
Please paste this code to a standard code module (that is one that you have to insert yourself - the default name will be like "Module1" but I recommend to give it a meaningful name: in my workbook it's "TXL_4828" which helps me find this thread again). Then save the workbook in XLSM format.
Option Explicit
' change the output language here:-
Const Outputs As String = "No,Yes"
Enum Nws ' worksheet navigation "ID_List"
' 117
NwsCapsRow = 1 ' change to suit
NwsFirstDataRow = 23 ' change to suit
NwsFirstID = 2 ' Columns: 2 = column B
NwsCode ' - do - (no set value = previous + 1)
End Enum
Enum Nwl ' worksheet navigation lists: "SAP" and "BOM"
' 117
NwlFirstDataRow = 3 ' change to suit
NwlId = 2 ' identical in both sheets
NwlCode ' - do - (no set value = previous + 1)
End Enum
Private Enum Ncs ' column set
' 117
NcsId ' first set column
NcsSap
NcsBom
NscSetCount
End Enum
Sub CheckAgainstLists()
' 117
Dim Ws As Worksheet
Dim SapId As Range ' ID column on SAP tab
Dim SapCode As Range ' Code column on SAP tab
Dim BomCode As Range ' Code column on BOM tab
Dim Result() As String
Dim GrpId As String ' Group ID (from column caption)
Dim Id As String
Dim C As Long ' loop counter: columns
Dim R As Long ' loop counter: rows
Set Ws = Worksheets("ID_List")
With Worksheets("SAP")
R = .Cells(.Rows.Count, NwlId).End(xlUp).Row
Set SapId = .Range(.Cells(NwlFirstDataRow, NwlId), .Cells(R, NwlId))
Set SapCode = .Range(.Cells(NwlFirstDataRow, NwlCode), .Cells(R, NwlCode))
End With
With Worksheets("BOM")
Set BomCode = .Range(.Cells(NwlFirstDataRow, NwlCode), _
.Cells(.Rows.Count, NwlCode).End(xlUp))
End With
Result = Split(Outputs, ",")
Application.ScreenUpdating = False
With Ws
For C = NwsFirstID To .Cells(NwsCapsRow, .Columns.Count) _
.End(xlToLeft).Column Step NscSetCount
GrpId = Trim(.Cells(NwsCapsRow, C).Value)
For R = NwsFirstDataRow To .Cells(.Rows.Count, C).End(xlUp).Row
Id = Trim(.Cells(R, C).Value)
.Cells(R, C + NcsSap).Value = Result(Sgn(WorksheetFunction.CountIfs(SapId, GrpId, SapCode, Id)))
.Cells(R, C + NcsBom).Value = Result(Sgn(WorksheetFunction.CountIf(BomCode, Id & "*")))
Next R
Next C
End With
Application.ScreenUpdating = True
End Sub
You can change the output language in the first Constant. Change rows and columns in the enums Nws and Nwl. Consider not touching anything else.
The code doesn't produce the result you expect. In the SAP column this may be due to the sample being smaller than your actual database. In the BOM column I attribute it to your result not finding the wild cards. Note that I interpreted your requirement "included" as "starting with". The code will not find "A18" in "xxxA18yyy". Use the worksheet functions to conduct manual searches to remove doubt about what the code does.
Last, not least, the code runs for about 5 seconds on the reduced sample. It will need more time if your db is bigger.
