Specialty Search with Calculation and Output

[smyers]

I have a sheet with three columns:
Column 1: Gear Ratio
Column 2: Horsepower Rating
Column 3: Model

There are six inputs:
Input 1: Specific Horsepower
Input 2: Minimum Service Factor
Input 3: Maximum Service Factor
Input 4: Minimum RPM
Input 5: Maximum RPM
Input 6: Actual Motor Speed

Based on the inputs, I would like to output a list of all Models, with corresponding Service Factor, Gear Ratio, and RPM which fall into the desired ranges for both Service Factor and RPM.

Service factor is a function of Horsepower and Horsepower Rating.
Service Factor = Horsepower Rating/Horsepower

RPM is a function of Actual Motor Speed and Gear Ratio.
RPM = Actual Motor Speed/Gear Ratio

I'm not sure if a VBA program is required to perform this. If so, suggested language would be great! I'm not a programmer, unfortunately.

Your Expert help is appreciated!!

 

[JB_Scotland]

You could do the query in situ. Use Data / Auto Filter on the motor specification table to show only those rows that match TRUE. An example below though I don't know if the values are representative.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 154px"><COL style="WIDTH: 135px"><COL style="WIDTH: 88px"><COL style="WIDTH: 117px"><COL style="WIDTH: 99px"><COL style="WIDTH: 34px"><COL style="WIDTH: 34px"><COL style="WIDTH: 49px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Inputs</TD><TD style="FONT-WEIGHT: bold"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Specific Horsepower</TD><TD style="TEXT-ALIGN: right">30</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Minimum Service Factor</TD><TD style="TEXT-ALIGN: right">0.8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Maximum Service Factor</TD><TD style="TEXT-ALIGN: right">1.7</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Minimum RPM</TD><TD style="TEXT-ALIGN: right">850</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Maximum RPM</TD><TD style="TEXT-ALIGN: right">1700</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Actual Motor Speed</TD><TD style="TEXT-ALIGN: right">1000</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Gear Ratio</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Horsepower Rating</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Model</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Service Factor</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">RPM</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">SF</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">RPM</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Match</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: center">A</TD><TD style="TEXT-ALIGN: center">0.766666667</TD><TD style="TEXT-ALIGN: center">200</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">FALSE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">0.5</TD><TD style="TEXT-ALIGN: center">333.3333333</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">FALSE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">D</TD><TD style="TEXT-ALIGN: center">0.266666667</TD><TD style="TEXT-ALIGN: center">142.8571429</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">FALSE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">45</TD><TD style="TEXT-ALIGN: center">E</TD><TD style="TEXT-ALIGN: center">1.5</TD><TD style="TEXT-ALIGN: center">250</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">FALSE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">33</TD><TD style="TEXT-ALIGN: center">F</TD><TD style="TEXT-ALIGN: center">1.1</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">FALSE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">28</TD><TD style="TEXT-ALIGN: center">G</TD><TD style="TEXT-ALIGN: center">0.933333333</TD><TD style="TEXT-ALIGN: center">1000</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">TRUE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: center">H</TD><TD style="TEXT-ALIGN: center">0.633333333</TD><TD style="TEXT-ALIGN: center">125</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">0</TD><TD style="TEXT-ALIGN: center">FALSE</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D10</TD><TD>=B10/$B$2</TD></TR><TR><TD>E10</TD><TD>=$B$7/A10</TD></TR><TR><TD>F10</TD><TD>=IF(AND(D10>$B$3,D10<$B$4),1,0)</TD></TR><TR><TD>G10</TD><TD>=IF(AND(E10>$B$5,E10<$B$6),1,0)</TD></TR><TR><TD>H10</TD><TD>=AND(F10,G10)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[smyers]

Thanks for looking at this, that's a great start. Is there any way to have an output which lists in a separate table the Model, Gear Ratio, RMP, and Service Factor of only the TRUE matches?

I have over 450 models with varying ratios and horsepower ratings and scrolling through the list to find true matches is not convenient for quick results.

Otherwise, your solution will work fine. Thanks again.

 

[JB_Scotland]

Select any cell in the motor specification table. Go to menu Data / Filter / Auto Filter. In the Match column select TRUE from the drop down list and the specification table will only show those models that match the query.

You can rearrange and hide columns that are used for calculating as you wish.

You could create VBA code to do the same thing with a user form for entering the query data. It would be a slicker interface but would require more work setting up.