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

 

[Fluff]

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))),"")

 

[Nova1979]

Sorry. All that seems to have done is copy WO Shortages line for line to results.

 

[Fluff]

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]

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]

You're welcome & thanks for the feedback

 

[Nova1979]

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]

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

 

[Nova1979]

As it is along the same line for help. Won’t it get locked/cancelled?

 

[Fluff]

Nope