Compare numbers to a tolerance range

**Palmetto** · 11-25-2009, 12:55 PM

It would help if you upload a sample workbook that duplicates the sheet structure and includes some data (don't need all ten sheets).

It would be best, IMO, if you consolidated the data into one worksheet as this would vastly simplify the task.

**C2AUTOINC** · 11-25-2009, 01:26 PM

I've attached an example. In the top I would like to be able the dimensions I have for the part. There will be at least ten of these tables that I would like to be able to search through using this method, it would be very easy to put them all on one sheet as well. I just need a way for it to find which standard and what Nominal Size that the washer would fall into. I'm not sure if there's a way to display the text of the size and table name at the top or even highlighting the row that it's in would be sufficient.

Thanks for any help!

Here's the table incase the attachment doesn't work:

ID OD Thickness
Enter Value Enter Value Enter Value

Commercial Flat Washers - 316 Stainless
OD ID Thickness
Size Max Min Max Min Max Min
1/4 0.63 0.62 0.286 0.276 0.055 0.045
1/4 0.693 0.683 0.286 0.276 0.055 0.045
5/16 0.755 0.745 0.348 0.338 0.055 0.045
3/8 0.88 0.87 0.411 0.401 0.055 0.045
3/8 1.005 0.995 0.411 0.401 0.055 0.045
1/2 1.255 1.245 0.567 0.557 0.067 0.057

**Palmetto** · 11-25-2009, 11:19 PM

See if the attached is helpful. There are two approaches.

On Sheet1
1. Advanced Filter automated with VBA. A dynamic named range was created to use as the source for the filter.

VBA code for Advanced Filter

Sub Filter_Data()

    Application.ScreenUpdating = False
    
    Sheet2.Range("C9").CurrentRegion.ClearContents

    Sheet1.Range("Database").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheet2.Range _
        ("C1:I2"), CopyToRange:=Sheet2.Range("C9"), Unique:=False
    
    Application.ScreenUpdating = True

End Sub

On Sheet2
2. Match formulas and Conditional Formatting

=IF(NOT(ISNA(MATCH($B$2,B8:B13,0))),OFFSET(INDIRECT(ADDRESS(MATCH($B$2,B8:B13,0)+7,2)),0,-1,1,1),IF(NOT(ISNA(MATCH($B$2,C8:C13,0))),

**C2AUTOINC** · 11-30-2009, 10:22 AM

Thanks, this was very helpful and in the right direction. The first sheet made the most sense to me. However, the numbers are a range of possible sizes, if i try to put in a size between the two numbers it does not recognize it being in the range. For example if i put in 0.05 as a thickness it should recognize most of the thicknesses as correct. Also if there was a way to highlight an entire row when all three of the criteria were met that would be helpful.

Thanks,
Chris

**Palmetto** · 11-30-2009, 01:28 PM

Formulas on sheet-1 amended. After further review, it only seems necessary to search in the one column for values related to a specific tolerance input.

=IF(NOT(ISNA(MATCH($B$2,C8:C13,1))),OFFSET($A$7,MATCH($B$2,C8:C13,1),0,1,1),"No Match")

Also amended the CF formulas to work with the tolerance ranges to highlight the entire row if all inputs fall within tolerance.

On Sheet2: revised criteria formula to improve matching tolerances by padding the values. If there are matches, then the AF returns blanks, otherwise all records are returned.

**C2AUTOINC** · 11-30-2009, 05:05 PM

Thank you very much for all of your help. Looking through page 1 I am starting to understand how some of these commands work, thank you. I am now trying to figure out if there is a way, similiar to how you were able to call out the 3/8 size corresponding to the value range, to display the standard. I attached a modified workbook from what you've sent me. The highlighting feature is very nice but there will be easily over 1,000 lines once all the data is enetered. Is there some sort of command that if all the conditions are true for a line that it can display the corresponding standard so that I won't have to scroll through to find the highlighted line?

Once again, thank you very much, I didn't know all of this was possible... I wish i understood the second page better but that seems to be beyond me right now so I've been concentrating on the first page.

Thanks,
Chris

**Palmetto** · 11-30-2009, 05:42 PM

I am now trying to figure out if there is a way, similiar to how you were able to call out the 3/8 size corresponding to the value range, to display the standard

See attached.

I wish i understood the second page better but that seems to be beyond me right now so I've been concentrating on the first page.

This is simply using Advanced Filter which has been automated using a bit of VBA code. The beauty of using Advanced Filter,even if not automated, is that you can extract a very specific set of records based on criteria you define.

It is well worth learning to use. Advanced Filter Basics