Excel Filter a range of cells based on the cell contents, ie: min & max removal

[K Wyman]

Hello wizards,
I have a worksheet that has five data points entered for up to 15 different test locations on an item. I need to filter out the min and max value data point from each of the sets of five data points and retain the other three data points for each testing location (for hundreds of items). Those remaining three data points I can then use the Word Mail Merge function to auto fill in my report templates.

My question is: I do not know the best (read: "easiest") way to filter out the min and max value data points (that can appear anywhere within the set of five data points) so that Word Mail Merge can more easily find the three central data points from Excel to auto populate into my Word report template (that I sort of know what I'm doing there in Word).

For example, I have items, such as apples and oranges each has five data points:

[TABLE="width: 500"]
<tbody>[TR]
[TD]item[/TD]
[TD]d1[/TD]
[TD]d2[/TD]
[TD]d3[/TD]
[TD]d4[/TD]
[TD]d5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

All I can manage at this point is to use the Excel Conditional Formatting feature to visually identify which data points are the min and max from the set, which isn't really helpful for generating data to auto fill into a template in Word using the Mail Merge function.

[TABLE="width: 500"]
<tbody>[TR]
[TD]item[/TD]
[TD]d1[/TD]
[TD]d2[/TD]
[TD]d3[/TD]
[TD]d4[/TD]
[TD]d5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

How might I go about automatically filtering out the min and max values from a range of source data (ie: B2:F2) for each row (ie: B3:F3 and so on) so that I can have an area on a worksheet (in the same worksheet or a different one within the same workbook, which ever you think is easiest) that looks more like the below table so that the Word Mail Merge function can seamlessly pull the data from Excel into Word?

[TABLE="width: 500"]
<tbody>[TR]
[TD]item[/TD]
[TD]d1[/TD]
[TD]d2[/TD]
[TD]d3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I did not include any particulars about the Word template or report that I'm using, because that (I think) would be TMI. Sufficient to say, the Word document will fill out one report for apples with the data values of 3, 4, and 5 and then one for oranges with 4, 5, and 2.

I thank you for your sage advice and your time.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub MinMax()
   Dim i As Long
   
   For i = 2 To Range("A" & Rows.count).End(xlUp).Row
   With Range("B" & i).Resize(, 5)
      .Value = Evaluate(Replace("If(@=max(@),true,@)", "@", .Address))
      .Value = Evaluate(Replace("If(@=min(@),true,@)", "@", .Address))
      .SpecialCells(xlConstants, xlLogical).Delete xlToLeft
   End With
   Next i
End Sub

However if you have 2 min or max values the same, both of them will be removed.

 

[K Wyman]

I do truly appreciate the code. My personal issue is getting it incorporated into my project. I'll keep working at that aspect of it. I'm assuming the "A" and "B" are row and column references.

Just a simpler question, is the supplied code above particular to any version of Excel? I am using Excel 2016, or more precisely the Professional Plus 2016 version.

 

[Fluff]

Both A & B are column references and the code should work on any version of Xl