Extracing a Single Instance of Each Name

[andrewc]

Hi,

In column A of Sheet2 I have a list of names and in columns B and C are the associated sales and country for each name. This is the “raw data”.

For example, in A3 is the name “John” and B3 and C3 have “1,000” and “France”, respectively.

I’m trying to create some VBA code that will summarise this data in Sheet1 (starting in A1), by putting a single instance of each country in column A and in column B the total sales for that country calculated from the data described above (much like a pivot table would calculate).

To help me on my way could someone please suggest the code that will copy a single instance of each country in the raw data to the Sheet1?

Thanks very much!

[daffodil11]

I'm sure there's a variety of ways; I went with an advanced filter of unique values of Column C and then just made a SUMIF of those values.

Sub andrewc()

    'dimension variables
    Dim lngLastSource As Long, lngLastSummary
    Dim wsData As Worksheet: Set wsData = Sheets("data")
    Dim wsSummary As Worksheet: Set wsSummary = Sheets("Summary")
    
    'clear summary
    wsSummary.Range("A1").CurrentRegion.ClearContents
    
    'identify max row of source
    lngLastSource = wsData.Range("A" & Rows.Count).End(xlUp).Row
    
    'filter unique countries
    wsData.Range("C1:C" & lngLastSource).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=wsSummary.Range("A1"), Unique:=True
        
    'create total column header
    wsSummary.Range("B1") = "Total"
    
    'identify max row of uniques
    lngLastSummary = wsSummary.Range("A" & Rows.Count).End(xlUp).Row
    
    'loop through all uniques
    For i = 2 To lngLastSummary
    
      'sumif for each
      wsSummary.Cells(i, 2) = WorksheetFunction.SumIf(wsData.Range("C2:C" & lngLastSource), _
                                                      wsSummary.Cells(i, 1), _
                                                      wsData.Range("B2:B" & lngLastSource))
    Next
        
End Sub

A loop could to simply run a continuous sum would probably work as well.

[andrewc]

That's great, thanks very much!

[AB33]

As alternative, I have used the data from daffodil11, but the code will not work in Mac.