2

Not sure if this is even possible. Its as follows, imagine the following excel sheet:

STOCK|VALUE|TAGS
ABCDE|10000|Z,X  
FGHIJ|20000|X,Y
KLMNO|30000|X,Y,Z
PQRST|10000|Z,Y
Total|70000|

Now somewhere on the sheet or workbook, I want to generate percentages of what each tag consists of in respect to the total value. So for example, it would show me (dynamically generated based on 'TAGS' values used):

X = 60,000 / 85.71%
Y = 60,000 / 85.71%
Z = 50,000 / 71.43%

The first (and only) method that comes to mind is having an if statement for each and every possible 'TAG' value. However, this is long, cumbersome, dirty, inefficient and not dynamic. Surely there is a better way? Would prefer a strictly Excel solution; but if its not possible - VBA it would have to be I suppose...

//Edit > Non-working VBA that I have at this stage. I dont do VBA; so excuse the poor code. Currently this attempts to generate a list on the 5'th/6th column of the unique tag fields and sum them up with a percentage field in the 7th column. Its not working in current state because its taking the whole tag field as a single value.

Sub ProcessData1()

Dim dict As Dictionary
Dim i As Integer
Dim targetRow As Integer
Dim stock As String
Dim value As Double
Dim more As Boolean

Set dict = New Dictionary

more = True
'Row Start
i = 2
targetRow = 1

While more
    tags = Worksheets("holdings").Cells(i, 3).value

    If dict.Exists(tags) Then
        value = Worksheets("holdings").Cells(dict.Item(tags), 6) + Worksheets("holdings").Cells(i, 2).value
        Worksheets("holdings").Cells(dict.Item(tags), 6) = value
        'Assumes Total value on Row 21
        Worksheets("holdings").Cells(dict.Item(tags), 7) = value * 100 / Worksheets("holdings").Cells(21, 2)
    Else
        targetRow = targetRow + 1
        Worksheets("holdings").Cells(targetRow, 5) = Worksheets("holdings").Cells(i, 3).value
        Worksheets("holdings").Cells(targetRow, 6) = Worksheets("holdings").Cells(i, 2).value
        Worksheets("holdings").Cells(targetRow, 7) = Worksheets("holdings").Cells(i, 2).value * 100 / Worksheets("holdings").Cells(21, 2)
        dict.Item(tags) = targetRow
    End If

    i = i + 1

    If Len(Worksheets("holdings").Cells(i, 1)) = 0 Then more = False
Wend

End Sub
  • Yes, sorry - you are right - I will edit the question... – SupaMonkey Aug 28 '18 at 18:51
3

You can use SUMIFS with wildcards:

=SUMIFS(B:B,C:C,"*" & E1 & "*")/$B$5

enter image description here

  • Thanks; anyway of making this dynamically generated though? That is to say, X,Y,Z are known values so you have 'hardcoded' them in E column. However, I want to be able to add tags in 'C' at will and have the list in 'E' dynamically adapt as I add/remove tags in 'C' column. – SupaMonkey Aug 28 '18 at 18:54
  • That would require an extensive array formula, so vba would be better. – Scott Craner Aug 28 '18 at 19:00
  • I thought VBA would be necessary; dont think I should repost this in Stack Overflow though if you maybe have the answer? – SupaMonkey Aug 28 '18 at 19:01
  • 1
    No, you would be down voted and closed. Both here and there like to see what you have tried. I have a lower threshold for formulas. But vba, a specific problem with existing code is needed when asking a question. – Scott Craner Aug 28 '18 at 19:03
  • Added the current VBA code I have thats missing a piece of the puzzle... Not sure if there is a way to 'explode' a value in excel? – SupaMonkey Aug 28 '18 at 19:58
0

Ok, so I've come up with my own answer. Its VBA and I dont do VBA as I said above so the code below I'm sure is far from a perfect solution but it does the trick.

Sub ProcessData1()

Dim dict As Dictionary
Dim i As Integer
Dim j As Integer
Dim targetRow As Integer
Dim stock As String
Dim value As Double
Dim more As Boolean
Dim tags() As String
Dim tag As String

Set dict = New Dictionary

more = True
'Row Start
i = 2
targetRow = 1

While more
    tags() = Split(Worksheets("holdings").Cells(i, 3).value, ",")

    For j = LBound(tags) To UBound(tags)
        tag = tags(j)
        'MsgBox tag

        If dict.Exists(tag) Then
            value = Worksheets("holdings").Cells(dict.Item(tag), 6) + Worksheets("holdings").Cells(i, 2).value
            Worksheets("holdings").Cells(dict.Item(tag), 6) = value
            'Assumes Total value on Row 21
            Worksheets("holdings").Cells(dict.Item(tag), 7) = value * 100 / Worksheets("holdings").Cells(21, 2)
        Else
            targetRow = targetRow + 1
            Worksheets("holdings").Cells(targetRow, 5) = tag
            Worksheets("holdings").Cells(targetRow, 6) = Worksheets("holdings").Cells(i, 2).value
            Worksheets("holdings").Cells(targetRow, 7) = Worksheets("holdings").Cells(i, 2).value * 100 / Worksheets("holdings").Cells(21, 2)
            dict.Item(tag) = targetRow
        End If
    Next j

    i = i + 1

    If Len(Worksheets("holdings").Cells(i, 1)) = 0 Then more = False
Wend

End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.