creating a list based on 2 exact criteria

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
104
Office Version
  1. 2010
Platform
  1. Windows
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
 

Attachments

  • Results.JPG
    Results.JPG
    48.6 KB · Views: 11
  • WO Shortages.JPG
    WO Shortages.JPG
    65.2 KB · Views: 11
  • WS Report.JPG
    WS Report.JPG
    93.1 KB · Views: 6

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

COwen

Board Regular
Joined
Feb 27, 2020
Messages
225
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

Board Regular
Joined
Feb 4, 2020
Messages
104
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Feb 27, 2020
Messages
225
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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.

VBA Code:
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

Board Regular
Joined
Feb 4, 2020
Messages
104
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Feb 27, 2020
Messages
225
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

Board Regular
Joined
Feb 4, 2020
Messages
104
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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?
 

Attachments

  • sheet refs.JPG
    sheet refs.JPG
    27 KB · Views: 3
  • Macro Run.JPG
    Macro Run.JPG
    107.3 KB · Views: 1

COwen

Board Regular
Joined
Feb 27, 2020
Messages
225
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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...

1587050012272.png
1587050029541.png
1587050050388.png
 

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
104
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,626
Office Version
  1. 365
Platform
  1. Windows
Can you post some sample data from both sheets using the XL2BB add-in?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,779
Messages
5,626,831
Members
416,203
Latest member
Rebmit1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top