Unique values in multiple columns

Posted on


I have a spreadsheet with the following:-

Prod No     Store 1 $    Store 1 Qty    Store 2 Sale    Store 2 Qty  etc
A               4.00         1             7.50            2
B               0            0             15.00           1
C               4.00         2              -8             -1
D               5.00         1              5.00           1

I need to get all the unique prices for each part no and then total the quantity for each prices

Prod No A has 4.00 1 unit and 7.50 2 units
Prod No B has 0.00 0 units and 15.00 1 unit
Prod No C has 4.00 2 units and -8 -1 unit
Prod No D has 5.00 2 units

I also need a list of just the unique prices and total of quantities for each price


4.00 3 units
7.50 2 units
0.00 0 units
-8 -1 unit
15.00 1 unit
5.00 2 units


First off, I reckon you would have more luck in the long run if you were storing your data in a more horizontal form, e.g.

Store      Product     Qty    Sales
Store 1    A           1      4.00
Store 1    B           0      0.00
Store 1    C           2      4.00
Store 1    D           0      0.00

It is much easier to do lookups on a single column, rather than pairs of columns.

(Depending on the size and scale, an Access database with separate Store, Product and Sales tables might even be better than that)

That said, if you are stuck with what you have, and you can stomach a VBA macro in your sheet, you can try the following:

  1. Add a Class Module to your VBA project, called Tuple, containing:

    Private szKey As String
    Private nValue As Double
    Public Property Get Key() As String
        Key = szKey
    End Property
    Public Property Let Key(newKey As String)
        szKey = newKey
    End Property
    Public Property Get Value() As Double
        Value = nValue
    End Property
    Public Property Let Value(newValue As Double)
        nValue = newValue
    End Property
  2. Add a normal Module, e.g. Module 1 to your project, containing:

    Public Function Summarize(ByRef rng As Range) As String
        If rng.Cells.Count Mod 2 = 1 Then Err.Raise 100, "", "Expected range of even cells"
        Dim coll As New Collection
        On Error Resume Next
        Dim flag As Boolean: flag = False
        Dim prevCel As Range, cel As Range: For Each cel In rng.Cells
            If flag Then
                Dim Key As String: Key = "" & prevCel.Value2
                coll(Key).Value = coll(Key).Value + cel.Value2
                If Err.Number <> 0 Then
                    Dim t1 As New Tuple
                        t1.Key = "" & prevCel.Value2
                        t1.Value = cel.Value2
                        coll.Add t1, Key
                    Set t1 = Nothing
                End If
            End If
            Set prevCel = cel
            flag = Not flag
        Next cel
        On Error GoTo 0
        Dim t2 As Variant: For Each t2 In coll
            If Len(Summarize) Then Summarize = Summarize & ", "
            Summarize = Summarize & Format(t2.Key, "#0.00") & " @ " & t2.Value
        Next t2
    End Function
  3. Then, in the worksheet, you could enter a formula, such as:

    ="Product " & $A2 & " has " & Summarize($B2:$I2)

    Make sure that you substitute the range $B2:$I2 for one that is wide enough to cover all possible numbers of Stores. Also make sure that you use an even-sized range (because the Sale/Qty values are in pairs) otherwise you’ll get a #VALUE error.

Leave a Reply

Your email address will not be published. Required fields are marked *