Unique values in multiple columns

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
e.g.,

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

e.g.,

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
                   Err.Clear
                   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.