Add all of Column A's, dependant on unique values in Column B

[gray_b]

I just cannot get my head around looping.
But I am trying to add all of Column A's, dependant on unique values in Column B
Any ideas please.

A B
Total Country
3 australia
3 australia
4 australia
2 Canada
2 Finland
2 Canada
3 Norway
3 ttxxyyzz
3 australia
4 Norway
4 australia
5 Finland
5 Italy
5 Italy
10 Finland

Result

A B
Grand Total Country
17 australia
4 Canada
17 Finland
7 Norway
3 ttxxyyzz
10 Italy

I can sort column B into unique values
Range("B2:B16").Select
Selection.Copy
Range("E2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$E$2:$E$16").RemoveDuplicates Columns:=1, Header:=xlNo

 

[Fluff]

How about with a formula

Fluff.xlsm
	A	B	C	D	E
1	Total	Country		Country	Total
2	3	australia		australia	17
3	3	australia		Canada	4
4	4	australia		Finland	17
5	2	Canada		Italy	10
6	2	Finland		Norway	7
7	2	Canada		ttxxyyzz	3
8	3	Norway
9	3	ttxxyyzz
10	3	australia
11	4	Norway
12	4	australia
13	5	Finland
14	5	Italy
15	5	Italy
16	10	Finland
Data

Cell Formulas
Range	Formula
D1:E7	D1	=GROUPBY(B1:B100,A1:A100,SUM,3,0,,B1:B100<>"")
Dynamic array formulas.

 

[gray_b]

That is excellent. I have got to read up on it, as to how that works.

I would never have thought that.

 

[gray_b]

How do I implement that with just VBA

 

[gray_b]

I think =GROUPBY(B1:B16, A1:A16, SUM,3,0) works well for me.

But what effect does the following do
,,B1:B100<>""

 

[Eric W]

Here's a VBA solution:

Sub test1()
Dim dic As Object, MyTable As Variant, i As Long

    Set dic = CreateObject("Scripting.Dictionary")
    MyTable = Range("A2:B16").Value
    
    For i = 1 To UBound(MyTable)
        dic(MyTable(i, 2)) = dic(MyTable(i, 2)) + MyTable(i, 1)
    Next i
    
    Range("G1:H1") = Array("Grand Total", "Country")

    Range("G2").Resize(dic.Count).Value = WorksheetFunction.Transpose(dic.items)
    Range("H2").Resize(dic.Count).Value = WorksheetFunction.Transpose(dic.keys)
    
End Sub

The Scripting DIctionary works well to find unique values. Make sure to change the input and output ranges to match your sheet.

 

[Fluff]

But what effect does the following do
,,B1:B100<>""

It filters the data to only show rows where col B is not blank.

 

[gray_b]

It filters the data to only show rows where col B is not blank.

In using the code in my real application

=GROUPBY(B1:B1149,A1:A1149,SUM,3,0,,B1:B1149<>"")

It runs into a problem, as it stops processing values 'Product Quantity' from A33.
It completes filling in all unique 'Product Name' in column B, but all values 'Product Quantity' from A33 are set to 0.

 

[Fluff]

That looks as though the values in A34 & below are actually text & not numbers.
Are they manually entered or a formula?

 

[gray_b]

Yes you are correct.

In checking the formatting, from A2 to A1149 they all show as 'general', but only A34 to A46 have 'top left error green triangle'

This data was exported from a stock control program, but not sure why A1 to A33 were processed correctly, as they are still formatted 'general'.

So am I right in assuming that even though a cells formatting shows as 'general' it can still be either a number or text

It all works fine now.

Very much appreciated, thanks