Maximum & Minimum values - filtered columns across sheets

**naik** · 02-06-2011, 08:11 AM

Hi,

This is my first post in this site (though I have read the forums for resolving my earlier doubts).

As a sample, I have attached an excel with the data.
I am trying to find maximum value of Profits in each year 2008, 2009 & 2010 (Columns B, C & D) for each unique item (Column A) from various sheets (Company-A & Company-B).
E.g., for Item number 165, the maximum profit in 2010 is 738 and minimum profit is -34477 and so on.
I would need the results in a separate sheet for each unique Item no.

So far, I have only been able to find an approach by filtering column A for each item value, then using subtotal formula to display the maximum/minimum values of columns B, C & D in cells and then copy using vba them to a new sheet.
I am not familiar with Pivot tables.

Can anyone please guide whether there is an alternative method which I am not aware of?
VBA code is most welcome.

Advance thanks

EDIT: Cross posted at Ozgrid & Mrexcel

**zbor** · 02-06-2011, 08:43 AM

How about Pivot table:

**Palmetto** · 02-06-2011, 09:09 AM

Cross posted here: Ozgrid

and here: MrExcel

**MickG** · 02-06-2011, 10:46 AM

Try this is new sheet:-

Sub MG06Feb06
Dim Rng As Range, Dn As Range
Dim n As Integer
Dim Sht As Variant
Dim Q
Dim Ac As Integer
Dim Temp As Double
Dim col As Integer
For Sht = 0 To UBound(Array("company-A", "company-B"))
With Sheets(Array("company-A", "company-B")(Sht))
    Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
End With
With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
        For Each Dn In Rng
            If Not .Exists(Dn.Value) Then
                .Add Dn.Value, Array(Dn, Dn(, 2), Dn(, 3), Dn(, 4))
            Else
                    Q = .Item(Dn.Value)
                     For n = 1 To 3
                        Q(n) = IIf(Abs(Dn(, n + 1)) > 0, Dn(, n + 1), Q(n))
                      Next n
                         .Item(Dn.Value) = Q
            End If
        Next
col = IIf(Sht = 1, 5, 1)
Cells(1, col).Resize(.Count, 4) = Application.Transpose(Application.Transpose(.items))
End With
Next Sht
End Sub

Regards Mick

**naik** · 02-06-2011, 12:10 PM

Resolved. Thanks a lot zbor & Mick for your quick replies. You guys & this forum rocks!!

zbor's solution meets my requirement perfectly! Though I am not familiar with Pivot tables, somehow I have worked this out.

I consolidated the data to a single sheet and used Pivot table to get the desired data.

Mick: Thank you for your solution too. Yet to understand the logic; will take quite some time for me to comprehend

Guess, I will have to go one step further from this and again compare min/max values of each item from each sheet to get the desired results.