creating a list based on 2 exact criteria

[Nova1979]

Hello All,

Ideally I would like a formula to do this if it is at all possible

I am needing to run a 2 criteria search from WO Shortages (column B and E) against WS Report (column C and M) and only create a list in Results that does not have a match
The match MUST equal both criteria. If only one of the criteria is matched, then it should add to the list in Results
In the supplied sample images, I have highlighted 3 rows, these are matched on both requirements of the criteria from WO Shortages in WS Report based . I have placed the highlighted rows in the Results away from the list to indicate that it is that they should not be part of the main list that is returned. (These rows should not appear in the actual Results sheet)

If I have not explained enough, please let me know
Unfortunatley I am unable to utilse XL2BB hence the screen shots, so I appologise for this

Thanks in advance

 

[COwen]

You would like to remove the items from the row and put them in Results if, for example, both PO#s match? Or is it the other way around where you want only values that are on one list but not the other?

Because your data will be in different rows of the two sources, I will need to use VBA. I cannot think of a good way to use a formula.

 

[Nova1979]

I need it so that if both the PO and the Part# from WO shortages matchesmatch, it is NOT in the results

Example - WO Shortages column B and column E (B4 AABDE80156 and E4 001670804) is searched for in WS Report and match in column C and column M exactly (C2 and M2 in this case)- This will NOT appear in the Results as both criteria are matched
- WO Shortages B7 AABDk78911 and E7010601624 is searched for in WS Report and only 1 or none match - This WILL appear in Results

I hope I have cleared this up

Thanks

 

[COwen]

You'll need to adapt it to your situation. This will only find one instance. You will need to add some code if you want it to carry down all of the possible matches.

Sub Nova1979()
Dim C1
Dim C2
Dim B
Dim E

For C1 = 2 To 1000
If Sheet3.Cells(C1, 2).Value <> "" Then
B = Sheet3.Cells(C1, 2).Value
E = Sheet3.Cells(C1, 5).Value
        For C2 = 2 To 1000
        If Sheet3.Cells(C2, 2).Value <> "" Then
        If Sheet3.Cells(C2, 3).Value = B Then
        If Sheet3.Cells(C2, 13).Value = E Then
        Sheet3.Range("N13").Value = B
        Sheet3.Range("O13").Value = E
        Else
        Sheet3.Range("N13").Value = "Not Found"
        Sheet3.Range("O13").Value = "Not Found"
        End If
        End If
        End If
        Next C2
End If
Next C1

End Sub

 

[Nova1979]

Sorry for my ignorance. I have no idea regarding VBA, how it works or even implementing it (hence why I was hoping for formula), however, I am happy to try.
Can you please give me a rundown on how to install and run the VBA. The most I know is ALT + F11 will bring up the relative screen

My skill level relating to excel is a bit more than a beginner

As for the searching, I do need this to carry down to find all instances as there are some 500 plus lines to be checked.
The Names of the sheets and the column references are all the same in the Workbook as the samples I provided.

Thanks

 

[COwen]

I will be happy to implement this into a workbook if you share to a cloud location such as OneDrive or GitHub or something like that. With the screenshots you provided, I was not able to get a grasp of your data.

Otherwise, to implement it yourself, press ALT + F11 to get to the editor. The click "Insert" in the task bar. Click "Module" and the paste the code. For is to work, you will need to make all of the "Sheet3" references in the code to whatever the sheet NUMBER is---NOT the name. To find this, on the left hand side of the editor, you will see all of the sheets in the book find the name of the sheet then in parentheses will be the number.

Next, in your sheet go to the insert tab and insert a rectangle. Right click and "AssignMacro" then click "Nova1979"---Because that is what I named the code.

 

[Nova1979]

I am unable to share due to security restrictions.
I have copied the VBA, and understand what you are saying with the sheet number change, however I am unsure of which sheet number goes where. I have attached a screen shot of the sheets

Once the VBA is corrected, I am not sure of the insert you are mentioning. I have found where to run the Macro. Is this right?

 

[COwen]

Okay, perhaps I misunderstood. Your ranges that you mentioned above are on different sheets? If this is the case then it will be a little more intense.


As for the Insert...

 

[Nova1979]

You are correct. There are 3 sheets involved. 1 Workbook

Criteria 1 - WS Report (sheet4) column C is the same as Results (sheet1) column B Criteria 2 - WS Report (sheet4) column M is the same as Results (sheet1) column E

I originaly thought of simply duplicating the the WO Shortages (sheet2) onto Results (sheet1).
WS Report (sheet4) would then be searched by row and Results (sheet1) would only remove rows where both the criteria are matched.

If only one of the criteria match, this row would be left inResults and the search would continue to the end of the data

Is a process like this more viable even using VBA?

 

[Fluff]

Can you post some sample data from both sheets using the XL2BB add-in?