# creating a list based on 2 exact criteria

#### Nova1979

##### Board Regular
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

#### Attachments

• Results.JPG
48.6 KB · Views: 11
• WO Shortages.JPG
65.2 KB · Views: 11
• WS Report.JPG
93.1 KB · Views: 6

#### Fluff

##### MrExcel MVP, Moderator
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

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### Nova1979

##### Board Regular
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.

#### Nova1979

##### Board Regular
This is the WO Shortages sheet

 Req Code ITEM NAME QTY REQ QTY ISSUED AABDH78636 005401962 HOSE, NONMETALLIC 15 12 AABDH78911 010737802 PIN, STRAIGHT, HEADED 4 0 AABDE80156 001670804 WASHER, FLAT 1 0 AABDB79672 008675804 SEALER, CHEMICAL 3 0 AABDA77978 011710003 DECAL 2 0 AABDK78991 010601624 PLUG, PROTECTIVE, DUST AND MOISTURE SEAL 8 2 AABDA78276 013410024 HOSE ASSEMBLY, NONMETALLIC 1 0 AABDB79876 010724492 BRACKET, ANGLE 1 0 AABDB80086 011109968 HOSE, PREFORMED 1 0 AABDC79928 015195505 RETAINER ASSEMBLY, R 2 0 AABDC79928 012865700 RELAY ASSEMBLY 1 0 AABDE79583 010695930 TUBE AND FITTINGS, METALLIC 1 0 AABDE79583 010672278 HOSE AND TUBE ASSEMBLY 1 0 AABDE79990 997615671 GREASE, AIRCRAFT AND INSTRUMENT 2 0 AABDF79530 013918457 DRIVE UNIT, ELECTROHYDRAULIC 1 0 AABDH79387 011049098 TUBING, NONMETALLIC 1 0 AABDJ79848 015918985 BOX, HANDSET STORAGE 1 0 AABDJ80376 013828319 MARKER, IDENTIFICATION 6 0 AABDK78867 014223906 WASHER, LOCK 2 0 AABDA78331 013823221 CONTROL, INDICATOR 1 0 AABDA78331 013823218 CONTROL, INTERCOMMUNICATION SET 5 0 AABDA79033 015752006 PROCESSOR, COMMUNICATIONS 1 0 AABDC79655 011645837 SCREW, ASSEMBLED WASHER 16 9 AABDC79655 013259836 STOP LIGHT-TAILLIGHT, VEHICULAR 1 0 AABDC79655 013962826 HEATER, VEHICULAR, COMPARTMENT 1 0 AABDC79655 011710003 DECAL 2 0 AABDC79930 015711255 POWER CONDITIONING 1 0 AABDD80572 010839011 CYLINDER ASSEMBLY, ACTUATING, LINEAR 1 0 AABDD80572 010748941 TRACK, SKATE MOUNT 1 0 AABDD80576 012060171 HANDLE ASSEMBLY 1 0 AABDE79898 661637818 ENGINE, GAS TURBINE, NONAIRCRAFT 1 0 AABDF80334 011978329 SPRING, FLAT 6 0 AABDF80334 011953975 KNOB 6 0 AABDF80334 011953878 PIN, STRAIGHT, HEADLESS 6 0 AABDF80334 011949902 PLUNGER, BREECH 2 0 AABDF80420 016170932 CURTAIN, VEHICULAR 1 0 AABDG79972 013819746 RING, RETAINING 3 0 AABDG79972 013259836 STOP LIGHT-TAILLIGHT, VEHICULAR 2 0 AABDH79274 007278239 ELBOW, TUBE 1 0 AABDH79559 013501397 HOSE ASSEMBLY, METALLIC 2 0 AABDH80196 013891725 WASHER, LOCK 4 0 AABDH80196 012045806 REPAIR KIT, MANUAL D 1 0 AABDH80196 015189181 VALVE, ANGLE 1 0 AABDJ80269 013962826 HEATER, VEHICULAR, COMPARTMENT 1 0 AABDJ80475 015098858 RHNB TEST SET, ELECT 1 0 AABDB80726 010560875 ADHESIVE 1 0 AABDB80814 011947951 PLATE, IDENTIFICATION 10 1 AABDB80814 013416244 GUARD, MECHANICAL DRIVE 1 0 AABDB80814 013599658 SHIELD ASSEMBLY, PROTECTIVE 1 0

#### Nova1979

##### Board Regular
This is the WS Report Sheet

 Requisition Number (Sold-to’s PO no.) Product Product Short Description Quantity AABDH78636 005401962 HOSE, NONMETALLIC 15 AABDH78911 010737802 PIN, STRAIGHT, HEADED 4 AABDE80156 001670804 WASHER, FLAT 1 AABDB79672 008675804 SEALER, CHEMICAL 3 AABDA77978 011710003 DECAL 2 AABDA79145 015147369 RHNB WITH CONTAINER 1 AABDH80205 012010814 HAND PUMP ASSEMBLY 1 AABDH80205 009547422 LUBRICANT, SOLID FILM 2 AABDH80205 009547422 LUBRICANT, SOLID FILM 2 AABDC79647 013756852 WASHER, FLAT 0 AABDB79942 007725519 CLAMP, LOOP 5 AABDB79942 011024737 SPACER, PLATE 2 AABDB79944 013294837 GAGE, PRESSURE, DIAL INDICATING 1 AABDB79944 011791352 ADAPTER, STRAIGHT, TUBE TO BOSS 1 AABDB79944 010991571 TUBE ASSEMBLY, METAL 1 AABDB79944 009848540 CLAMP, LOOP 1 AABDB79944 010798220 COUPLING, TUBE 2 AABDB79944 011553870 TEE, TUBE 1 AABDB79944 011501040 TUBE ASSEMBLY, METAL 1 AABDB79944 010996461 TUBE, BENT, METALLIC 1 AABDB79942 002934208 WIRE, NONELECTRICAL 1 AABDB79942 011023573 SPACER, PLATE 2 AABDB79942 013203696 SEAL, NONMETALLIC STRIP 1 AABDG79811 012862092 SPACER, SLEEVE 1 AABDG79811 011887736 HOSE ASSEMBLY, NONMETALLIC 1 AABDG79810 011714774 ELECTRONIC COMPONENTS ASSEMBLY 1 AABDJ80003 012169769 SEAL, NONMETALLIC ANGLE 1 AABDF79673 010724223 BEARING, WASHER, THRUST 6 AABDF79673 012033125 SEAL, NONMETALLIC ROUND SECTION 6 AABDF79673 012434846 TUBING, NONMETALLIC 2 AABDE79718 013205628 PERISCOPE, ARMORED VEHICLE 1 AABDG79834 001515013 BEARING, PLAIN, ROD END 1 AABDG79834 013868187 NUT, PLAIN, HEXAGON 1 AABDG79834 013803578 SCREW, MACHINE 2 AABDG79834 013787694 NUT, PLAIN, HEXAGON 2 AABDG79834 011846699 TUBE ASSEMBLY, METAL 1 AABDA78397 010739762 BUSHING, SLEEVE 1 AABDA78397 008548709 PIN, GROOVED, HEADLESS 1 AABDE79734 012960785 GUARD, MECHANICAL DRIVE 1 AABDA78404 012113675 SWITCH, SENSITIVE 1 AABDK79041 011028207 BELL CRANK 2 AABDK79041 012761652 PUSH ON NUT 30 AABDJ80053 010835355 SEAL, PLAIN 1 AABDJ80053 010832991 GUARD, HOSE-TUBING 1 AABDC79749 010748974 PIN, GROOVED, HEADED 3 AABDC79749 007151152 RING, RETAINING 4 AABDK79058 011337472 RUBBER STRIP 6 AABDB80023 011147698 SEAL, NONMETALLIC SPECIAL SHAPED SECTION 2

#### Nova1979

##### Board Regular

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.

 AABDA79031 010986717 TUBE ASSEMBLY, METAL AABDB80650 015567126 NUT, SELF-LOCKING, HEXAGON AABDB80650 015921504 NUT, PLAIN, HEXAGON AABDC80414 001668411 O-RING AABDC80414 010737848 GASKET AABDC80414 010996443 HOSE ASSEMBLY, NONMETALLIC AABDC80414 011000486 TUBE ASSEMBLY, METAL AABDC80414 012278990 TUBE ASSEMBLY, METAL AABDC80416 014729942 0​ AABDC80416 010996444 0​ AABDC80416 011916228 0​ AABDC80418 011835350 0​ AABDC80418 121792980 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

#### Fluff

##### MrExcel MVP, Moderator

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

#### Nova1979

##### Board Regular

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

#### Nova1979

##### Board Regular
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.

#### Fluff

##### MrExcel MVP, Moderator
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))),"")

#### Nova1979

##### Board Regular
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

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

Replies
8
Views
135
Replies
11
Views
301
Replies
1
Views
67
Replies
4
Views
315
Replies
1
Views
55

1,127,449
Messages
5,624,827
Members
416,057
Latest member
VARSHA V VASWANI

### 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.

### Which adblocker are you using?

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

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