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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,470
Office Version
  1. 365
Platform
  1. Windows
How about
=IFERROR(INDEX('WO Shortages'!A$2:A$1000,AGGREGATE(15,6,(ROW('WO Shortages'!$B$2:$B$1000)-ROW('WO Shortages'!$B$2)+1)/(('WO Shortages'!$B$2:$B$1000<>'WS Report'!$C$2:$C$1000)*('WO Shortages'!$E$2:$E$1000<>'WS Report'!$M$2:$M$1000)), ROWS(A$2:A2))),"")
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
104
Office Version
  1. 2010
Platform
  1. Windows
Sorry. All that seems to have done is copy WO Shortages line for line to results.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,470
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
=IFERROR(INDEX('WO Shortages'!A$2:A$3000,AGGREGATE(15,6,(ROW('WO Shortages'!$B$2:$B$3000)-ROW('WO Shortages'!$B$2)+1)/((ISERROR(MATCH('WO Shortages'!$B$2:$B$3000,'WS Report'!$C$2:$C$3000,0)))*(ISERROR(MATCH('WO Shortages'!$E$2:$E$3000,'WS Report'!$M$2:$M$3000,0)))), ROWS(A$2:A2))),"")
 

Nova1979

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

I have run the formula on a trial and it looks to be working. I will do a complete run early next week and let you know.

Thanks again
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,470
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You're welcome & thanks for the feedback
 

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
104
Office Version
  1. 2010
Platform
  1. Windows
Hello Fluff,
The formula works extremely well.
I am wondering if it is possible to add a third component regarding quantities or is that getting too complicated?

The above formula is run and finds a matching item from WO Shortages in WS Report. This would be excluded from the list being creadted based on the 2 existing criteria.
A third requirement is then searched which checks WO Shortages column X (quantity required) against WS Report column O (quantity issued). If the numbers matched it is excluded.
If the issue quantity is less than required then it is listed

If this is not possible, or is overly complex, I am extremely happy with the supptort and help you have already provided.

Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,470
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I think it's best if you start a new thread for this. Thanks.
 

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
104
Office Version
  1. 2010
Platform
  1. Windows
As it is along the same line for help. Won’t it get locked/cancelled?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,422
Messages
5,624,699
Members
416,042
Latest member
Oden

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