Displaying multiple matches based on multiple criteria.

Joe Rdz

New Member
Joined
Nov 16, 2017
Messages
6
Hi, so I've been looking around for a kind of self-project that I have in mind. I got a list of different descriptions with variable components, basically my idea is: if I have a certain amount of this component and a certain amount of this other component, display all the matches based on the criteria AND quantity needed (Because of the available material).

Excel 2016 (Windows) 64 bit

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​

Mounting

Qty Required

Description

Color
Mouting

IS​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

STD​

1​
RSX1 LED P1 40K R4 HVOLT WBA DBLXD
DBLXD
Quantity

50​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​

IS​

2​
RSXF1 LED P4 50K WFL 240 IS PEX FAO DDBXD
DDBXD
Color

DDBXD​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​

STD​

48​
RSX1 LED P4 50K R3 MVOLT SPA DNAXD
DNAXD

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​

IS​

8​
RSXF2 LED P4 40K WFL MVOLT IS DNAXD
DNAXD
Mouting

IS​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​

IS​

2​
RSXF1 LED P4 50K WFL MVOLT IS PE DWHXD
DWHXD
Quantity

30​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​

IS​

53​
RSXF1 LED P4 50K WFL MVOLT IS PE FAO DDBXD
DDBXD
Color

DNAXD​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​

STD​

9​
RSX1 LED P1 40K R3 240 HS SPA NLTAIR2 PIRHN DDBXD
DDBXD

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​

STD​

5​
RSX2 LED P2 40K R5 240 SPA NLTAIR2 PIRHN DDBXD
DDBXD
Mouting

STD​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​

IS​

10​
RSXF2 LED P6 50K WFL MVOLT IS FAO DDBXD
DDBXD
Quantity

60​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​

IS​

16​
RSXF2 LED P6 50K WFL MVOLT IS FAO DDBXD
DDBXD
Color

DDBXD​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]12[/COLOR]​

IS​

12​
RSXF1 LED P4 40K WFL MVOLT IS FAO DDBXD
DDBXD

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]13[/COLOR]​

STD​

2​
RSX1 LED P1 40K R4 MVOLT HS SPA DDBXD
DDBXD
Mouting

STD​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]14[/COLOR]​

STD​

5​
RSX1 LED P4 40K R4 MVOLT SPA DDBXD
DDBXD
Quantity

DWHXD​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]15[/COLOR]​

STD​

2​
RSX1 LED P3 50K R4 MVOLT SPA PE DDBXD
DDBXD
Color

10​

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]16[/COLOR]​

STD​

2​
RSX2 LED P3 30K R3 MVOLT SPA DDBXD
DDBXD

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]17[/COLOR]​

STD​

1​
RSX1 LED P2 40K R4 MVOLT SPA DDBXD
DDBXD

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]18[/COLOR]​

STD​

10​
RSX1 LED P3 40K R4 MVOLT RPA DDBXD
DDBXD

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]19[/COLOR]​

MA​

2​
RSX2 LED P4 50K R5 MVOLT MA DNATXD
DNATXD

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]20[/COLOR]​

MA​

8​
RSX2 LED P4 50K R3 MVOLT MA DNATXD
DNATXD

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]21[/COLOR]​

STD​

10​
RSX2 LED P4 40K R4 480 RPA DDBXD
DDBXD

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]22[/COLOR]​

IS​

1​
RSXF1 LED P4 50K WFL MVOLT IS PER7 DDBXD
DDBXD

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]23[/COLOR]​

STD​

4​
RSX1 LED P4 40K R5 MVOLT RPA DDBXD
DDBXD

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]24[/COLOR]​

STD​

8​
RSX1 LED P4 40K R5 MVOLT RPA DDBXD
DDBXD

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]25[/COLOR]​

STD​

2​
RSX1 LED P4 40K R5 MVOLT RPA DDBXD
DDBXD

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]26[/COLOR]​

STD​

1​
RSX1 LED P1 40K R3 MVOLT RPA DDBXD
DDBXD

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]27[/COLOR]​

STD​

4​
RSX1 LED P4 50K R5 MVOLT SPA DDBXD
DDBXD

<tbody>
</tbody>
Sheet: Sheet1

<tbody>
</tbody>

what I've got in F:G is the table that I want to use as criteria and the list on the left is the one to filter based on it.

Could anyone guide me on this?
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
Hi Joe,

Can you use this? Copy the array formula across and down to suit total possible rows.

The Mounts & Colors are data validated into B1 & B3.

I'm not sure how to work your Qty criteria into the filter though;

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">Mouting</td><td style="text-align: right;;">STD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">Mounting</td><td style="font-weight: bold;;">Colors</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="font-weight: bold;;">Quantity</td><td style="text-align: right;;">50</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">IS</td><td style=";">DBLXD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="font-weight: bold;;">Color</td><td style="text-align: right;;">DDBXD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">STD</td><td style=";">DDBXD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="font-weight: bold;;">Count Records</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">MA</td><td style=";">DNAXD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">DWHXD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="font-weight: bold;;">Total Qty Req'd</td><td style="text-align: right;;">65</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">DNATXD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="font-weight: bold;;">Mounting</td><td style="font-weight: bold;;">Qty Required</td><td style="font-weight: bold;;">Description</td><td style="font-weight: bold;;">Color</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">STD</td><td style="text-align: right;;">9</td><td style=";">RSX1 LED P1 40K R3 240 HS SPA NLTAIR2 PIRHN DDBXD</td><td style=";">DDBXD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">STD</td><td style="text-align: right;;">5</td><td style=";">RSX2 LED P2 40K R5 240 SPA NLTAIR2 PIRHN DDBXD</td><td style=";">DDBXD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">STD</td><td style="text-align: right;;">2</td><td style=";">RSX1 LED P1 40K R4 MVOLT HS SPA DDBXD</td><td style=";">DDBXD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">STD</td><td style="text-align: right;;">5</td><td style=";">RSX1 LED P4 40K R4 MVOLT SPA DDBXD</td><td style=";">DDBXD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">STD</td><td style="text-align: right;;">2</td><td style=";">RSX1 LED P3 50K R4 MVOLT SPA PE DDBXD</td><td style=";">DDBXD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">STD</td><td style="text-align: right;;">2</td><td style=";">RSX2 LED P3 30K R3 MVOLT SPA DDBXD</td><td style=";">DDBXD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">STD</td><td style="text-align: right;;">1</td><td style=";">RSX1 LED P2 40K R4 MVOLT SPA DDBXD</td><td style=";">DDBXD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">STD</td><td style="text-align: right;;">10</td><td style=";">RSX1 LED P3 40K R4 MVOLT RPA DDBXD</td><td style=";">DDBXD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">STD</td><td style="text-align: right;;">10</td><td style=";">RSX2 LED P4 40K R4 480 RPA DDBXD</td><td style=";">DDBXD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">STD</td><td style="text-align: right;;">4</td><td style=";">RSX1 LED P4 40K R5 MVOLT RPA DDBXD</td><td style=";">DDBXD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">STD</td><td style="text-align: right;;">8</td><td style=";">RSX1 LED P4 40K R5 MVOLT RPA DDBXD</td><td style=";">DDBXD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">STD</td><td style="text-align: right;;">2</td><td style=";">RSX1 LED P4 40K R5 MVOLT RPA DDBXD</td><td style=";">DDBXD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">STD</td><td style="text-align: right;;">1</td><td style=";">RSX1 LED P1 40K R3 MVOLT RPA DDBXD</td><td style=";">DDBXD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">STD</td><td style="text-align: right;;">4</td><td style=";">RSX1 LED P4 50K R5 MVOLT SPA DDBXD</td><td style=";">DDBXD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">=COUNTIFS(<font color="Blue">Sheet1!$A$2:$A$27,$B$1,Sheet1!$D$2:$D$27,$B$3</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B6</th><td style="text-align:left">=SUMIF(<font color="Blue">$A$9:$A$29,$B$1,$B$9:$B$29</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A9</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$A$9:A9</font>)>$B$4,"",INDEX(<font color="Red">Sheet1!A$2:A$27,SMALL(<font color="Green">IF(<font color="Purple">Sheet1!$A$2:$A$27&Sheet1!$D$2:$D$27=$B$1&$B$3,ROW(<font color="Teal">Sheet1!$A$2:$A$27</font>)-ROW(<font color="Teal">Sheet1!$A$2</font>)+1</font>),ROWS(<font color="Purple">$A$9:A9</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,114,337
Messages
5,547,355
Members
410,785
Latest member
phillippaige
Top