Can Excel generate a percentage chart from a column with CSV values? How?

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

You can use SUMIFS with wildcards:

=SUMIFS(B:B,C:C,"*" & E1 & "*")/\$B\$5 • 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
• 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

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