Sum of values between sets of parentheses with specific strings

[Country_Calc]

I need to calculate the sum of values between sets of parentheses with specific text strings (which are 3 letter country codes) within the same cell.

Here is the how the data is made available. This has to be across multiple rows.

Source Data

[TABLE="width: 500"]
<tbody>[TR]
[TD]BGR (1), CZE (5), DEU (9), NLD (6), POL (4), RUS (3), ZAF (8), GBR (15)[/TD]
[/TR]
[TR]
[TD]RUS (4), ESP (5), SWE (3), UKR (8), GBR (4)[/TD]
[/TR]
</tbody>[/TABLE]


Desired Result on another worksheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]CZE[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]RUS[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]GBR[/TD]
[TD]19[/TD]
[/TR]
</tbody>[/TABLE]

I tried to search the board, but all the examples I found only had to deal with 1 set of parentheses per cell.

Please let me know if this is possible.

 

[Rick Rothstein]

See if this macro produces the output you want (output going to Sheet2)...

Sub TotalParensPerCountryCode()
  Dim X As Long, Z As Long, Data As Variant, Codes() As String
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  With CreateObject("Scripting.Dictionary")
    For X = 1 To UBound(Data)
      Codes = Split(Replace(Data(X, 1), " ", ""), ",")
      For Z = 0 To UBound(Codes)
        .Item(Left(Codes(Z), 3)) = .Item(Left(Codes(Z), 3)) - Mid(Codes(Z), 4)
      Next
    Next
    Sheets("Sheet2").Range("A1").Resize(.Count) = Application.Transpose(.Keys)
    Sheets("Sheet2").Range("B1").Resize(.Count) = Application.Transpose(.Items)
  End With
End Sub

 

[Country_Calc]

Yes, this does work so long as the source data starts in A1.

I am struggling to set up a custom range in the macro as this would be used in a template where the data would always be in the "I" column.

Data = Range("A1", Cells(Rows.Count, "A").End(xlUp)) - This works

Data = Range("$I:$I", Cells(Rows.Count, "A").End(xlUp)) - does not work, gives a type mismatch error in line .Item(Left(Codes(Z), 3)

Data = Range(countries, Cells(Rows.Count, "A").End(xlUp)) - does not work as named range, gives a range of object _global failed on the same line

I really appreciate the help and can work with the A1 scenario if I have to, but I am trying to perfect this as there will be new data all the time.

Thanks,

Country Calc

 

[Rick Rothstein]

Yes, this does work so long as the source data starts in A1.

You did not specify where your data was located at in your original message... when you make us guess, we will pretty much guess cell A1, Column A or Row 1 (cell A2 or Row 2 if you tell us or hint at there being a header), whichever is applicable in the code or formula we write.

I am struggling to set up a custom range in the macro as this would be used in a template where the data would always be in the "I" column.

Data = Range("A1", Cells(Rows.Count, "A").End(xlUp)) - This works

Data = Range("$I:$I", Cells(Rows.Count, "A").End(xlUp)) - does not work

Proper syntax would be...

Data = Range("I1", Cells(Rows.Count, "A").End(xlUp))

Data = Range(countries, Cells(Rows.Count, "A").End(xlUp)) - does not work as named range, gives a range of object _global failed on the same line

If countries is an Excel Defined Name, then it must be quoted (otherwise VBA will think it is the name of a variable) and, since the Defined Name already encompasses all of the cells, you can use it directly...

Data = Range("countries")