creating a list based on 2 exact criteria

N

Nova1979

Board Regular
Joined
Feb 4, 2020
Messages
111
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: 14
  • WO Shortages.JPG
    WO Shortages.JPG
    65.2 KB · Views: 13
  • WS Report.JPG
    WS Report.JPG
    93.1 KB · Views: 9
Sort by date Sort by votes
Try this in A2 of your Result sheet, copied down
=INDEX('WO Shortages'!A2:A100,AGGREGATE(15,6,(ROW('WO Shortages'!B2:B100)-ROW('WO Shortages'!B2)+1)/(('WO Shortages'!B2:B100<>'WS Report'!C2:C100)+('WO Shortages'!E2:E100<>'WS Report'!M2:M100)), ROWS(A$2:A2)))

It may need Ctrl Shift Enter
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
I might be able to come up with something from home, but that wont be for a while. I am very limited during the day due to where I work and the securtiy surounding the IT systems.
 
Upvote 0
This is the WO Shortages sheet

Req CodeITEM NAME QTY REQQTY ISSUED
AABDH78636005401962HOSE, NONMETALLIC1512
AABDH78911010737802PIN, STRAIGHT, HEADED40
AABDE80156001670804WASHER, FLAT10
AABDB79672008675804SEALER, CHEMICAL30
AABDA77978011710003DECAL20
AABDK78991010601624PLUG, PROTECTIVE, DUST AND MOISTURE SEAL82
AABDA78276013410024HOSE ASSEMBLY, NONMETALLIC10
AABDB79876010724492BRACKET, ANGLE10
AABDB80086011109968HOSE, PREFORMED10
AABDC79928015195505RETAINER ASSEMBLY, R20
AABDC79928012865700RELAY ASSEMBLY10
AABDE79583010695930TUBE AND FITTINGS, METALLIC10
AABDE79583010672278HOSE AND TUBE ASSEMBLY10
AABDE79990997615671GREASE, AIRCRAFT AND INSTRUMENT20
AABDF79530013918457DRIVE UNIT, ELECTROHYDRAULIC10
AABDH79387011049098TUBING, NONMETALLIC10
AABDJ79848015918985BOX, HANDSET STORAGE10
AABDJ80376013828319MARKER, IDENTIFICATION60
AABDK78867014223906WASHER, LOCK20
AABDA78331013823221CONTROL, INDICATOR10
AABDA78331013823218CONTROL, INTERCOMMUNICATION SET50
AABDA79033015752006PROCESSOR, COMMUNICATIONS10
AABDC79655011645837SCREW, ASSEMBLED WASHER169
AABDC79655013259836STOP LIGHT-TAILLIGHT, VEHICULAR10
AABDC79655013962826HEATER, VEHICULAR, COMPARTMENT10
AABDC79655011710003DECAL20
AABDC79930015711255POWER CONDITIONING10
AABDD80572010839011CYLINDER ASSEMBLY, ACTUATING, LINEAR10
AABDD80572010748941TRACK, SKATE MOUNT10
AABDD80576012060171HANDLE ASSEMBLY10
AABDE79898661637818ENGINE, GAS TURBINE, NONAIRCRAFT10
AABDF80334011978329SPRING, FLAT60
AABDF80334011953975KNOB60
AABDF80334011953878PIN, STRAIGHT, HEADLESS60
AABDF80334011949902PLUNGER, BREECH20
AABDF80420016170932CURTAIN, VEHICULAR10
AABDG79972013819746RING, RETAINING30
AABDG79972013259836STOP LIGHT-TAILLIGHT, VEHICULAR20
AABDH79274007278239ELBOW, TUBE10
AABDH79559013501397HOSE ASSEMBLY, METALLIC20
AABDH80196013891725WASHER, LOCK40
AABDH80196012045806REPAIR KIT, MANUAL D10
AABDH80196015189181VALVE, ANGLE10
AABDJ80269013962826HEATER, VEHICULAR, COMPARTMENT10
AABDJ80475015098858RHNB TEST SET, ELECT10
AABDB80726010560875ADHESIVE10
AABDB80814011947951PLATE, IDENTIFICATION101
AABDB80814013416244GUARD, MECHANICAL DRIVE10
AABDB80814013599658SHIELD ASSEMBLY, PROTECTIVE10
 
Upvote 0
This is the WS Report Sheet

Requisition Number (Sold-to’s PO no.)ProductProduct Short DescriptionQuantity
AABDH78636005401962HOSE, NONMETALLIC15
AABDH78911010737802PIN, STRAIGHT, HEADED4
AABDE80156001670804WASHER, FLAT1
AABDB79672008675804SEALER, CHEMICAL3
AABDA77978011710003DECAL2
AABDA79145015147369RHNB WITH CONTAINER1
AABDH80205012010814HAND PUMP ASSEMBLY1
AABDH80205009547422LUBRICANT, SOLID FILM2
AABDH80205009547422LUBRICANT, SOLID FILM2
AABDC79647013756852WASHER, FLAT0
AABDB79942007725519CLAMP, LOOP5
AABDB79942011024737SPACER, PLATE2
AABDB79944013294837GAGE, PRESSURE, DIAL INDICATING1
AABDB79944011791352ADAPTER, STRAIGHT, TUBE TO BOSS1
AABDB79944010991571TUBE ASSEMBLY, METAL1
AABDB79944009848540CLAMP, LOOP1
AABDB79944010798220COUPLING, TUBE2
AABDB79944011553870TEE, TUBE1
AABDB79944011501040TUBE ASSEMBLY, METAL1
AABDB79944010996461TUBE, BENT, METALLIC1
AABDB79942002934208WIRE, NONELECTRICAL1
AABDB79942011023573SPACER, PLATE2
AABDB79942013203696SEAL, NONMETALLIC STRIP1
AABDG79811012862092SPACER, SLEEVE1
AABDG79811011887736HOSE ASSEMBLY, NONMETALLIC1
AABDG79810011714774ELECTRONIC COMPONENTS ASSEMBLY1
AABDJ80003012169769SEAL, NONMETALLIC ANGLE1
AABDF79673010724223BEARING, WASHER, THRUST6
AABDF79673012033125SEAL, NONMETALLIC ROUND SECTION6
AABDF79673012434846TUBING, NONMETALLIC2
AABDE79718013205628PERISCOPE, ARMORED VEHICLE1
AABDG79834001515013BEARING, PLAIN, ROD END1
AABDG79834013868187NUT, PLAIN, HEXAGON1
AABDG79834013803578SCREW, MACHINE2
AABDG79834013787694NUT, PLAIN, HEXAGON2
AABDG79834011846699TUBE ASSEMBLY, METAL1
AABDA78397010739762BUSHING, SLEEVE1
AABDA78397008548709PIN, GROOVED, HEADLESS1
AABDE79734012960785GUARD, MECHANICAL DRIVE1
AABDA78404012113675SWITCH, SENSITIVE1
AABDK79041011028207BELL CRANK2
AABDK79041012761652PUSH ON NUT30
AABDJ80053010835355SEAL, PLAIN1
AABDJ80053010832991GUARD, HOSE-TUBING1
AABDC79749010748974PIN, GROOVED, HEADED3
AABDC79749007151152RING, RETAINING4
AABDK79058011337472RUBBER STRIP6
AABDB80023011147698SEAL, NONMETALLIC SPECIAL SHAPED SECTION2
 
Upvote 0
Sorry, This is the best I can come up with at the current time.
If these are copied into a spread sheet, the should match the columns in the information I have provided. Due to the information, I have deleted what is in most of the cells. I appologise if this is causing issues.

Fluff,
When I have copied downas well as across, and your formula and it seems to work up to a point where the information is not transfered. Here is a sample of what is happening in the first 5 columns.

AABDA79031010986717TUBE ASSEMBLY, METAL
AABDB80650015567126NUT, SELF-LOCKING, HEXAGON
AABDB80650015921504NUT, PLAIN, HEXAGON
AABDC80414001668411O-RING
AABDC80414010737848GASKET
AABDC80414010996443HOSE ASSEMBLY, NONMETALLIC
AABDC80414011000486TUBE ASSEMBLY, METAL
AABDC80414012278990TUBE ASSEMBLY, METAL
AABDC80416014729942
0​
AABDC80416010996444
0​
AABDC80416011916228
0​
AABDC80418011835350
0​
AABDC80418121792980
0​

Where the cells are blank, I have deleted information. The issue lies where there is a 0. This is the result returned from the formula.
I am also getting the same issue in other columns, where the formula works up to a point, and then returns the 0 result
 
Upvote 0
Thanks for that, how about

+Fluff.xlsm
ABCDE
1POPart #ItemQty reqdQty issued
2AABDE801561670804WASHER, FLAT10
3AABDE801561670804WASHER, FLAT10
4AABDB796728675804SEALER, CHEMICAL30
5AABDB796728675804SEALER, CHEMICAL30
6AABDA7797811710003DECAL20
7AABDA7797811710003DECAL20
8AABDK7899110601624PLUG, PROTECTIVE, DUST AND MOISTURE SEAL82
9AABDK7899110601624PLUG, PROTECTIVE, DUST AND MOISTURE SEAL82
10AABDA7827613410024HOSE ASSEMBLY, NONMETALLIC10
11AABDA7827613410024HOSE ASSEMBLY, NONMETALLIC10
12AABDB7987610724492BRACKET, ANGLE10
13AABDB7987610724492BRACKET, ANGLE10
14AABDB8008611109968HOSE, PREFORMED10
15AABDB8008611109968HOSE, PREFORMED10
16AABDC7992815195505RETAINER ASSEMBLY, R20
17AABDC7992815195505RETAINER ASSEMBLY, R20
18AABDC7992812865700RELAY ASSEMBLY10
19AABDC7992812865700RELAY ASSEMBLY10
20AABDE7958310695930TUBE AND FITTINGS, METALLIC10
21AABDE7958310695930TUBE AND FITTINGS, METALLIC10
22AABDE7958310672278HOSE AND TUBE ASSEMBLY10
23AABDE7958310672278HOSE AND TUBE ASSEMBLY10
24AABDE79990997615671GREASE, AIRCRAFT AND INSTRUMENT20
25AABDE79990997615671GREASE, AIRCRAFT AND INSTRUMENT20
26AABDF7953013918457DRIVE UNIT, ELECTROHYDRAULIC10
27AABDF7953013918457DRIVE UNIT, ELECTROHYDRAULIC10
28AABDH7938711049098TUBING, NONMETALLIC10
29AABDH7938711049098TUBING, NONMETALLIC10
30AABDJ7984815918985BOX, HANDSET STORAGE10
31AABDJ7984815918985BOX, HANDSET STORAGE10
32AABDJ8037613828319MARKER, IDENTIFICATION60
33AABDJ8037613828319MARKER, IDENTIFICATION60
34AABDK7886714223906WASHER, LOCK20
35AABDK7886714223906WASHER, LOCK20
36AABDA7833113823221CONTROL, INDICATOR10
37AABDA7833113823221CONTROL, INDICATOR10
38AABDA7833113823218CONTROL, INTERCOMMUNICATION SET50
39AABDA7833113823218CONTROL, INTERCOMMUNICATION SET50
40AABDA7903315752006PROCESSOR, COMMUNICATIONS10
41AABDA7903315752006PROCESSOR, COMMUNICATIONS10
42AABDC7965511645837SCREW, ASSEMBLED WASHER169
43AABDC7965511645837SCREW, ASSEMBLED WASHER169
44AABDC7965513259836STOP LIGHT-TAILLIGHT, VEHICULAR10
45AABDC7965513259836STOP LIGHT-TAILLIGHT, VEHICULAR10
46     
47     
48     
49     
50
Result
Cell Formulas
RangeFormula
A2:A49A2=IFERROR(INDEX('WO Shortages'!B$2:B$100,AGGREGATE(15,6,(ROW('WO Shortages'!$B$2:$B$100)-ROW('WO Shortages'!$B$2)+1)/(('WO Shortages'!$B$2:$B$100<>'WS Report'!$C$2:$C$99)+('WO Shortages'!$E$2:$E$100<>'WS Report'!$M$2:$M$99)), ROWS(A$2:A2))),"")
B2:C49B2=IFERROR(INDEX('WO Shortages'!E$2:E$100,AGGREGATE(15,6,(ROW('WO Shortages'!$B$2:$B$100)-ROW('WO Shortages'!$B$2)+1)/(('WO Shortages'!$B$2:$B$100<>'WS Report'!$C$2:$C$99)+('WO Shortages'!$E$2:$E$100<>'WS Report'!$M$2:$M$99)), ROWS(B$2:B2))),"")
D2:E49D2=IFERROR(INDEX('WO Shortages'!X$2:X$100,AGGREGATE(15,6,(ROW('WO Shortages'!$B$2:$B$100)-ROW('WO Shortages'!$B$2)+1)/(('WO Shortages'!$B$2:$B$100<>'WS Report'!$C$2:$C$99)+('WO Shortages'!$E$2:$E$100<>'WS Report'!$M$2:$M$99)), ROWS(D$2:D2))),"")
 
Upvote 0
If I am reading that formula correctly it will remove the 0 and leave a blank cell.
the problem is that where the 0 is there should be the description of the part #
I have managed to use XL2BB. Here is a screen of the Results sheet with the initial formula you supplied.
SHORTAGES REPORT TRIAL2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
255AABDAABDB80650B806500001015567126NUT, SELF-LOCKING, HEXAGON w0GLZB ASD MCSRDRPSA08 0NAME4391743903AREAarea-5070SV20AABD3#NUM!15###0#NUM!00AABD0
256AABDAABDB80650B806500002015921504NUT, PLAIN, HEXAGON w0ACAH 0MCSRDRPSA08 0NAME4391743903AREAarea-5090SV10AABD3#NUM!0###0#NUM!00AABD0
257AABDAABDC80414C804140020001668411O-RING w0AC71 0MCSNIRPSA08 0NAME4391743903AREAarea-5110SV10AABD2#NUM!0###20#NUM!00AABD0
258AABDAABDC80414C804140037010737848GASKET w0ACDB AVD MCSNIRPSA08 0NAME4391743903AREAarea-5130SV20AABD2#NUM!0###65#NUM!043952AABD0
259AABDAABDC80414C804140041010996443HOSE ASSEMBLY, NONMETALLIC w0ACDB AVD MCSNIRPSA08 0NAME4391743903AREAarea-5150SV10AABD3#NUM!0###63#NUM!043857AABD0
260AABDAABDC80414C804140042011000486TUBE ASSEMBLY, METAL w0ACDB AVD MCSNIRPSA08 0NAME4391743903AREAarea-5170SV20AABD3#NUM!0###84#NUM!044044AABD0
261AABDAABDC80414C804140051012278990TUBE ASSEMBLY, METAL 00ACDB AVD MCSNIRPSA08 0NAME439174390300000000#NUM!0########NUM!######NUM!#NUM!#NUM!
262AABDAABDC80416C804160004014729942000ACDB AVD MCSNIRPSA08 0NAME04390300000000#NUM!0########NUM!######NUM!#NUM!#NUM!
263AABDAABDC80416C804160008010996444000ACDB AVD MCSNIRPSA08 0NAME04390300000000#NUM!0########NUM!######NUM!#NUM!#NUM!
264AABDAABDC80416C804160010011916228000ACDB AVD MCSNIRPSA08 0NAME04390300000000#NUM!0########NUM!######NUM!#NUM!#NUM!
265AABDAABDC80418C804180002011835350000ACDB AVD MCSNIRPSA08 0NAME04390400000000#NUM!0########NUM!######NUM!#NUM!#NUM!
266AABDAABDC80418C804180005121792980000ACDB AVD MCSNIRPSA08 0NAME04390400000000#NUM!0########NUM!######NUM!#NUM!#NUM!
267AABDAABDD80506D805060006013884137000ACDB AVD MCSNIRPSA08 0NAME04390400000000#NUM!0########NUM!######NUM!#NUM!#NUM!
268AABDAABDE80396E803960023010542585000ACLP MSD MCSNIRPSA08 0NAME04390300000000#NUM!0########NUM!######NUM!#NUM!#NUM!
269AABDAABDE80397E803970004010332830000ACDB AVD MCSNIRPSA08 0NAME04390300000000#NUM!0########NUM!######NUM!#NUM!#NUM!
Results
 
Upvote 0
I followed you instruction and placed
=INDEX('WO Shortages'!A2:A100,AGGREGATE(15,6,(ROW('WO Shortages'!B2:B100)-ROW('WO Shortages'!B2)+1)/(('WO Shortages'!B2:B100<>'WS Report'!C2:C100)+('WO Shortages'!E2:E100<>'WS Report'!M2:M100)), ROWS(A$2:A2)))
into A2 modified 100 to 1000 across the formula to increase the range and copied down. I then copied this across from column A to AS.
In the information above, where there is a 0 in column F, there should be a part description.

WO Shortages sheet has approximately 550 rows.
WS Report sheet has 3000 lines

Should have mentioned this earlier. Looking back at comments from COwen, I might be able to modify information enough through the workbook and share it on one drive.
Would this be of help

Thanks for people patience.
 
Upvote 0
You need to lock the ranges like
=IFERROR(INDEX('WO Shortages'!A$2:A$100,AGGREGATE(15,6,(ROW('WO Shortages'!$B$2:$B$100)-ROW('WO Shortages'!$B$2)+1)/(('WO Shortages'!$B$2:$B$100<>'WS Report'!$C$2:$C$100)+('WO Shortages'!$E$2:$E$100<>'WS Report'!$M$2:$M$100)), ROWS(A$2:A2))),"")
 
Upvote 0
Hi Fluff

Not sure what is happening. The last formula returns 2 rows of each item

I have modified the information enough to allow others access and placed it in one drive

OneDrive

Sign in to your OneDrive cloud storage and Office Online.
1drv.ms 1drv.ms

Can you please have a look and let me know what I have done wrong

Just to make sure I am clear on the process needed

WO Shortages is checked against WS Report (WS Report is parts that have been supplied)
This returns the Results which is a list of parts that have not been supplied

Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
index match with multiple criteria to create list
2 3
Replies
26
Views
348
Bob_ipc
B
excelbytes
  • Solved
COUNTIFS With Lists Of Multiple Criteria
Replies
5
Views
173
Fluff
Fluff
T
  • Question
Multiple filters on filtered list
Replies
1
Views
175
Cubist
C
M
  • Question
VBA code to create command button and copy criteria based data from one worksheet to another
2
Replies
13
Views
427
Tupe77
T
M
  • Question
Return "yes" or "no" matching multiple criteria
Replies
5
Views
2K
MG137
M

Forum statistics

Threads
1,214,421
Messages
6,119,392
Members
448,891
Latest member
tpierce

Share this page

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
Back
Top