Displaying multiple matches based on multiple criteria.

[Joe Rdz]

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?

 

[RasGhul]

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;

Book1
	A	B	C	D	E	F
1	Mouting	STD			Mounting	Colors
2	Quantity	50			IS	DBLXD
3	Color	DDBXD			STD	DDBXD
4	Count Records	14			MA	DNAXD
5						DWHXD
6	Total Qty Req'd	65				DNATXD
7
8	Mounting	Qty Required	Description	Color
9	STD	9	RSX1 LED P1 40K R3 240 HS SPA NLTAIR2 PIRHN DDBXD	DDBXD
10	STD	5	RSX2 LED P2 40K R5 240 SPA NLTAIR2 PIRHN DDBXD	DDBXD
11	STD	2	RSX1 LED P1 40K R4 MVOLT HS SPA DDBXD	DDBXD
12	STD	5	RSX1 LED P4 40K R4 MVOLT SPA DDBXD	DDBXD
13	STD	2	RSX1 LED P3 50K R4 MVOLT SPA PE DDBXD	DDBXD
14	STD	2	RSX2 LED P3 30K R3 MVOLT SPA DDBXD	DDBXD
15	STD	1	RSX1 LED P2 40K R4 MVOLT SPA DDBXD	DDBXD
16	STD	10	RSX1 LED P3 40K R4 MVOLT RPA DDBXD	DDBXD
17	STD	10	RSX2 LED P4 40K R4 480 RPA DDBXD	DDBXD
18	STD	4	RSX1 LED P4 40K R5 MVOLT RPA DDBXD	DDBXD
19	STD	8	RSX1 LED P4 40K R5 MVOLT RPA DDBXD	DDBXD
20	STD	2	RSX1 LED P4 40K R5 MVOLT RPA DDBXD	DDBXD
21	STD	1	RSX1 LED P1 40K R3 MVOLT RPA DDBXD	DDBXD
22	STD	4	RSX1 LED P4 50K R5 MVOLT SPA DDBXD	DDBXD
Sheet2

Cell Formulas
Range	Formula
B4	=COUNTIFS(Sheet1!$A$2:$A$27,$B$1,Sheet1!$D$2:$D$27,$B$3)
B6	=SUMIF($A$9:$A$29,$B$1,$B$9:$B$29)
A9	{=IF(ROWS($A$9:A9)>$B$4,"",INDEX(Sheet1!A$2:A$27,SMALL(IF(Sheet1!$A$2:$A$27&Sheet1!$D$2:$D$27=$B$1&$B$3,ROW(Sheet1!$A$2:$A$27)-ROW(Sheet1!$A$2)+1),ROWS($A$9:A9))))}
Press CTRL+SHIFT+ENTER to enter array formulas.