Modifying 2 criteria based formula to 3

Nova1979

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


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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,547
Office Version
  1. 365
Platform
  1. Windows
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

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

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

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

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. :unsure:
 

Nova1979

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

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

ADVERTISEMENT

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

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
104
Office Version
  1. 2010
Platform
  1. Windows
Hopeful concept. I am very happy with what you gave me in the other post.
 

Nova1979

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

Watch MrExcel Video

Forum statistics

Threads
1,127,572
Messages
5,625,584
Members
416,119
Latest member
JCLLE

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