Modifying 2 criteria based formula to 3

[Nova1979]

Hello All

I have recieved help with another post on 'creating a list based on 2 spercific criteria' from an extremely helpful and patient member.

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

The above formula is run and finds a matching item from WO Shortages in WS Report. A list on a new sheet is created excluding matches found based on the 2 existing criteria (which is matching row data).

I am hoping that a third requirement can be added into this formula. When searched it checks WO Shortages column X (quantity required) against WS Report column O (quantity issued). If the numbers matched it is excluded.
If the quantity issued is less than quantity required then it is listed.

Microsoft OneDrive - Access files anywhere. Create docs with free Office Online.

Store photos and docs online. Access them from any PC, Mac or phone. Create and work together on Word, Excel or PowerPoint documents.

1drv.ms

Here is a link to the one drive file.
I have not added the above formula to this. It is placed in Results sheet A2, copies across to AS2 then copied down

Thanks

 

[Fluff]

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)/(('WO Shortages'!$X$486:$X$3000>'WS Report'!$O$3:$O$3000)*(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]

Fluff
In the formula you have put )+1)/(('WO Shortages'!$X$486:$X$3000>'WS Report'!$O$3:$O$3000)*( should it be )+1)/(('WO Shortages'!$X$2:$X$3000>'WS Report'!$O$2:$O$3000)*(
Thanks

 

[Nova1979]

Hi have run the formula you have suggested. Not sure what it has done, but not what I am hoping for.

WO Shortages X2 has a qty of 15 required.
WS Report O1737 (column C ref AABDH78636) indicates only 12 have been supplied.

NOTE: WO Shortages column Y is also indicating issued quantities

Based on the original formula, it is excluded from the created list due to the criteria set.
As the full qty has not been supplied, I am hoping to include it into the created list increasing the returned results from 40 rows after running the original formula.

When I run the formula you have given, this decreases the returned results from 40 rows to 11.

 

[Fluff]

ADVERTISEMENT

I'm afraid I don't understand. If you want everything that has a shortage returned, then you will just get the entire shortage sheet again.

 

[Nova1979]

I am having to operate between 2 programs.
WO Shortages (items I require) will drop information into WS Report (items from supplier). Information will only move across if supplier has stock, even if supplier only has partial stock required. The problem I face is that once the full quantity of stock has been supplied, it stays in the WS Report.

The original idea was to create a list of everything that has NOT moved across

WO Shortages criteria 1 and 2 DO NOT match in WS Report then return a result (original formula)

WO Shortages criteria 1 and 2 DO match in WS Report then exclude from results (original formula)

I am hoping to be able to expand this to also identify items that have moved across where only partial quantity has been supplied. This means 1 or more has been issued to me.

WO Shortages criteria 1 and 2 DO match WS Report and 0 stock has been supplied then exclude from results. (This indicates the information has moved across but not yet been processed)

WO Shortages criteria 1 and 2 DO match WS Report and stock supplied is between 0 and the required amount then include this in the results (only a partial supply has been given which means supplier does not have enough)

WO Shortages criteria 1 and 2 DO match WS Report and the required stock matches the issued stock exactly then exclude from results. (This indicates the requirement has been met)

 

[Fluff]

ADVERTISEMENT

I'm afraid that's beyond me. Hopefully somebody else will step in and help.

 

[Nova1979]

Hopeful concept. I am very happy with what you gave me in the other post.

 

[Nova1979]

To anyone that happens here, this is what I am after.

Base formula (explained above):
=IFERROR(INDEX('WO Shortages'!A$2:A$4000,AGGREGATE(15,6,(ROW('WO Shortages'!$B$2:$B$4000)-ROW('WO Shortages'!$B$2)+1)/((ISERROR(MATCH('WO Shortages'!$B$2:$B$4000,'WMS Report'!$C$2:$C$4000,0)))*(ISERROR(MATCH('WO Shortages'!$E$2:$E$4000,'WMS Report'!$M$2:$M$4000,0)))), ROWS(A$2:A4))),"")

I am hoping to incorperate that if a matched result is found in the base formula then WO Shortages X2:X4000 checks against WS Report O2:O400.
If O2=0 or WO Shortages X2 and WS Report O2 match excatly then exclude from results
If O2 = less than X2 then include in results

Thanks