Narrowing a table based on criteria

[powell]

Hi board,

I'm having trouble doing the following, and I haven't been able to come up with a good enough search term to find a solved version of my problem.

I have about 7000 line items, but I'll simplify it to explain what I'm trying to do. Basically, I have a large table of item data that I want reduced to new table sets based on certain criteria that can be inputted into certain cells.

Sheet 1: Part Data

Part	Length	Width	Height	Weight	A	B	C
1	5	6	4	10
2	3	5	3	8
3	14	9	19	36
4	6	2	8	38
5	1	20	4	5

<tbody>
</tbody>

A B and C represent additional columns that I want to preserve in the new table, but wont be used for narrowing down items.


Sheet 2: Criteria and new (dynamic) Part table

Min Length	2
Max Length	10
Min Width	4
Max Width	10
Min Height	1
Max Height	14
Min Weight	5
Max Weight	20

<tbody>
</tbody>

For each of these criteria, I would like to be able to change the numbers, and have a table populate next to it with all the parts from sheet 1 that fit the criteria.

Let me know if any more clarification is needed. Any help would be greatly appreciated.

 

[Eric W]

Something like this?

Excel 2010

	A	B	C	D	E	F	G	H
1	Part	Length	Width	Height	Weight	A	B	C
2	1	5	6	4	10
3	2	3	5	3	8
4	3	14	9	19	36
5	4	6	2	8	38
6	5	1	20	4	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Excel 2010

	A	B	C	D	E	F	G	H	I
1		Min Length	2
2		Max Length	10
3		Min Width	4
4		Max Width	10
5		Min Height	1
6		Max Height	14
7		Min Weight	5
8		Max Weight	20
9
10	Row	Part	Length	Width	Height	Weight	A	B	C
11	2	1	5	6	4	10	0	0	0
12	3	2	3	5	3	8	0	0	0
13

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B11 =IF(A11="","",INDEX(Sheet1!A:A,$A11)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula A11 {=IF(A10="","",IFERROR(SMALL(IF((--(Sheet1!$B$2:$B$6>=$C$1))*(--(Sheet1!$B$2:$B$6<=$C$2))*(--(Sheet1!$C$2:$C$6>=$C$3))*(--(Sheet1!$C$2:$C$6<=$C$4))*(--(Sheet1!$D$2:$D$6>=$C$5))*(--(Sheet1!$D$2:$D$6<=$C$6))*(--(Sheet1!$E$2:$E$6>=$C$7))*(--(Sheet1!$E$2:$E$6<=$C$8)),ROW(Sheet1!$A$2:$A$6)),ROWS($A$11:$A11)),""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The A column on sheet2 is a helper column, just showing the row where a matching part is found. You can hide it if you want. Put the formula in A11, and confirm it with Control-Shift-Enter. Then enter the formula in B11. (Change the ranges to match your sheet.) Then copy B11 and copy it across to I11. Then select A11:I11 and paste them down the column as far as necessary.

Let me know how this works.