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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Part[/TD]
[TD]Length[/TD]
[TD]Width[/TD]
[TD]Height[/TD]
[TD]Weight[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]14[/TD]
[TD]9[/TD]
[TD]19[/TD]
[TD]36[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]8[/TD]
[TD]38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Min Length[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Max Length[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Min Width[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Max Width[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Min Height[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Max Height[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Min Weight[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Max Weight[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Part[/TD]
[TD="bgcolor: #FAFAFA"]Length[/TD]
[TD="bgcolor: #FAFAFA"]Width[/TD]
[TD="bgcolor: #FAFAFA"]Height[/TD]
[TD="bgcolor: #FAFAFA"]Weight[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA"]C[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]19[/TD]
[TD="bgcolor: #FAFAFA, align: right"]36[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]38[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

</tbody>

Sheet1​

Excel 2010

	A	B	C	D	E	F	G	H	I
Row	Length	Width	Height	Weight	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Min Length[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Max Length[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Min Width[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Max Width[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Min Height[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Max Height[/TD]
[TD="bgcolor: #FAFAFA, align: right"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Min Weight[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Max Weight[/TD]
[TD="bgcolor: #FAFAFA, align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="bgcolor: #FAFAFA"]Part[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]13[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B11[/TH]
[TD="align: left"]=IF(A11="","",INDEX(Sheet1!A:A,$A11))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A11[/TH]
[TD="align: left"]{=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)),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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.