Calculation from text string

matthew.armitage

Board Regular
Joined
Nov 23, 2009
Messages
72
Hi All

I have got a web report that exports to a text string with the quantity and product. Its always in the same format, starts with "Selection:" and the the "quantity" "x" "product" and then comma. A couple of examples are below:

Selection: 1 x Rose ,1 x Pistachio ,2 x Coffee ,2 x Lemon ,2 x Chocolate ,1 x Nutella ,1 x Raspberry ,1 x Chocolate Mint ,1 x Vanilla ,1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Earl Grey ,1 x Strawberries & Cream ,1 x Chocolate Chilli Mango
Selection: 1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Strawberries & Cream ,1 x Raspberry ,1 x Nutella
Selection: 3 x Pistachio ,1 x Vanilla ,1 x Chocolate ,1 x Rose

What is the best way to get a grand total for each type for the entire list.

I can do it do as a one off excerise using text to colums but takes ages, so would like to automate so I can paste the report into my file and sum automatically.

Thanks

Matt
 
Please see below steps:
1601823291067.png

1601823308409.png

1601823378018.png

1601823448900.png

1601823464700.png

1601823492967.png


After changed the datasource, Refresh and you will get the updated result.

1601823541338.png
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Remember to change the Table name Table34 to the name of your table.
Rich (BB code):
= Table.Group(Table.TransformColumns(Table.Combine(List.Transform(Excel.CurrentWorkbook(){[Name="Table34"]}[Content][Column1],each Table.FromRows(List.Transform(Text.Split(Text.AfterDelimiter(_,": ")," ,"),(x)=>Text.Split(x," x "))))),{"Column1",Number.From}),"Column2",{"Total",each List.Sum([Column1])})
 
Upvote 0
Try:
VBA Code:
Sub matthew()
    Application.ScreenUpdating = False
    Dim Val As String, Val2 As Variant, desWS As Worksheet, prod As String
    Dim i As Long, ii As Long, arr As Variant, dic As Variant, qty As Long, tot As Long, key As Variant
    Set desWS = Sheets("Sheet2")
    arr = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(arr) To UBound(arr)
        Val = Trim(Split(arr(i, 1), ":")(1))
        Val2 = Split(Val, ",")
        For ii = LBound(Val2) To UBound(Val2)
            prod = Trim(Mid(Mid(Val2(ii), WorksheetFunction.Find("x", Val2(ii)), 99999), 3, 99999))
            If Not dic.Exists(prod) Then
                dic.Add prod, Trim(tot + Left(Val2(ii), WorksheetFunction.Find("x", Val2(ii)) - 1))
            Else
                dic.Item(prod) = CLng(dic.Item(prod)) + CLng(Trim(tot + Left(Val2(ii), WorksheetFunction.Find("x", Val2(ii)) - 1)))
            End If
        Next ii
    Next i
    With desWS
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(dic.Count, 2).Value = Application.Transpose(Array(dic.keys, dic.items))
    End With
    Application.ScreenUpdating = True
End Sub
Results will be in Sheet2. Change the range to suit your needs.
 
Last edited:
Upvote 0
A possible solution via macro:
VBA Code:
Sub PistacchioAndC()
Dim FOrder As String, PandC(), PArr()
Dim cX As Long, I As Long, cComma As Long
Dim cQt As String, cPist As String, myMatch, cUB As Long
Dim SData As Range, OutTable As Range

Set SData = Sheets("Foglio5").Range("A1")       '<<< The beginning of the Source Data
Set OutTable = Sheets("Foglio5").Range("B10")   '<<< The beginning of the desired Output Table
'
ReDim PandC(1 To 2, 1 To 1)
ReDim PArr(1 To 1)
For I = 1 To 1000
    If SData.Cells(I, 1) = "" Then Exit For
    FOrder = FOrder & " , " & SData.Cells(I, 1)
Next I
For I = 1 To Len(FOrder)
    cX = InStr(I, FOrder, "x", vbTextCompare)
    If cX = 0 Then Exit For
    cComma = InStr(cX, FOrder & "    ", ",", vbTextCompare)
    If cComma = 0 Then cComma = Len(FOrder) + 2
    cQt = Replace(Trim(Mid(FOrder, cX - 3, 3)), ",", "")
    cPist = Trim(Replace(Mid(FOrder, cX, cComma - cX), "x", "", , , vbTextCompare))
    myMatch = Application.Match(cPist, PArr, False)
    If IsError(myMatch) Then
        cUB = UBound(PArr)
        ReDim Preserve PArr(1 To cUB + 1)
        ReDim Preserve PandC(1 To 2, 1 To cUB + 1)
        myMatch = cUB + 1
        PArr(myMatch) = cPist
        PandC(1, myMatch) = cPist
    End If
    If IsNumeric(cQt) Then PandC(2, myMatch) = PandC(2, myMatch) + CLng(cQt)
    I = cX
Next I
PandC(1, 1) = "Flavour"
PandC(2, 1) = "Qty"
OutTable.Resize(UBound(PandC, 2), UBound(PandC, 1)).Value = Application.WorksheetFunction.Transpose(PandC)
End Sub
The lines marked <<< have to be adapted to your situation.
Put the code in a Standard Module of your Vba Project and customize the 2 lines marked <<<; then run the Sub PistacchioAndC

Bye

Edit: In the meantime, other solutions have been proposed by @mumps and @shaowu459 ; see previous messages
 
Upvote 0
Have you tried the macro I suggested in Post #13?
 
Upvote 0
Remember to change the Table name Table34 to the name of your table.
Rich (BB code):
= Table.Group(Table.TransformColumns(Table.Combine(List.Transform(Excel.CurrentWorkbook(){[Name="Table34"]}[Content][Column1],each Table.FromRows(List.Transform(Text.Split(Text.AfterDelimiter(_,": ")," ,"),(x)=>Text.Split(x," x "))))),{"Column1",Number.From}),"Column2",{"Total",each List.Sum([Column1])})

I have added a date column, could you amend the query so that it sums the a total of each type by the date?

Excel challenge.xlsx
AB
1Column1Column2
2Line Item PropertiesDate
3Selection: 3 x Chocolate ,3 x Raspberry ,3 x Pistachio ,3 x Lemon ,3 x Nutella ,3 x Chocolate Mint01-Oct-2020
4Selection: 6 x Chocolate Mint01-Oct-2020
5Selection: 2 x Strawberries & Cream ,2 x Salted Caramel ,2 x Pumpkin Spice ,4 x Coffee ,2 x Rose ,2 x Vanilla ,2 x Lemon ,2 x Nutella01-Oct-2020
6Selection: 2 x Pumpkin Spice ,2 x Raspberry ,2 x Lemon01-Oct-2020
7Selection: 1 x Rose ,1 x Pistachio ,1 x Coffee ,1 x Lemon ,1 x Chocolate ,2 x Nutella ,1 x Raspberry ,1 x Chocolate Mint ,1 x Vanilla ,1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Earl Grey ,2 x Strawberries & Cream ,2 x Chocolate Chilli Mango01-Oct-2020
8Selection: 1 x Strawberries & Cream ,1 x Vanilla ,1 x Raspberry ,1 x Nutella ,1 x Salted Caramel ,1 x Chocolate Mint01-Oct-2020
9Selection: 3 x Pumpkin Spice ,3 x Chocolate Chilli Mango01-Oct-2020
10Selection: 3 x Pumpkin Spice ,2 x Chocolate Mint ,1 x Salted Caramel01-Oct-2020
11Selection: 2 x Pistachio ,1 x Coffee ,1 x Salted Caramel ,2 x Pumpkin Spice ,2 x Chocolate Chilli Mango ,2 x Earl Grey ,2 x Chocolate Mint ,2 x Vanilla ,2 x Raspberry ,1 x Lemon ,1 x Rose01-Oct-2020
12Selection: 3 x Pistachio ,1 x Vanilla ,1 x Chocolate ,1 x Rose01-Oct-2020
13Selection: 3 x Rose ,3 x Lemon01-Oct-2020
14Selection: 1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Strawberries & Cream ,1 x Raspberry ,1 x Nutella01-Oct-2020
15Selection: 1 x Chocolate Chilli Mango ,1 x Salted Caramel ,1 x Pumpkin Spice ,1 x Chocolate Mint ,1 x Chocolate ,1 x Coffee01-Oct-2020
Sheet1
 
Upvote 0
Since your data has column headers, so check the checkbox to make the first line as headers.
1601888826781.png


I changed the date in column B and the result is:

Book1.xlsx
ABCDEF
1Line Item PropertiesDateColumn1Column3Total
2Selection: 3 x Chocolate ,3 x Raspberry ,3 x Pistachio ,3 x Lemon ,3 x Nutella ,3 x Chocolate Mint1-Oct-201-Oct-20Chocolate3
3Selection: 6 x Chocolate Mint1-Oct-201-Oct-20Raspberry5
4Selection: 2 x Strawberries & Cream ,2 x Salted Caramel ,2 x Pumpkin Spice ,4 x Coffee ,2 x Rose ,2 x Vanilla ,2 x Lemon ,2 x Nutella1-Oct-201-Oct-20Pistachio3
5Selection: 2 x Pumpkin Spice ,2 x Raspberry ,2 x Lemon1-Oct-201-Oct-20Lemon7
6Selection: 1 x Rose ,1 x Pistachio ,1 x Coffee ,1 x Lemon ,1 x Chocolate ,2 x Nutella ,1 x Raspberry ,1 x Chocolate Mint ,1 x Vanilla ,1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Earl Grey ,2 x Strawberries & Cream ,2 x Chocolate Chilli Mango2-Oct-201-Oct-20Nutella5
7Selection: 1 x Strawberries & Cream ,1 x Vanilla ,1 x Raspberry ,1 x Nutella ,1 x Salted Caramel ,1 x Chocolate Mint2-Oct-201-Oct-20Chocolate Mint9
8Selection: 3 x Pumpkin Spice ,3 x Chocolate Chilli Mango2-Oct-201-Oct-20Strawberries & Cream2
9Selection: 3 x Pumpkin Spice ,2 x Chocolate Mint ,1 x Salted Caramel2-Oct-201-Oct-20Salted Caramel2
10Selection: 2 x Pistachio ,1 x Coffee ,1 x Salted Caramel ,2 x Pumpkin Spice ,2 x Chocolate Chilli Mango ,2 x Earl Grey ,2 x Chocolate Mint ,2 x Vanilla ,2 x Raspberry ,1 x Lemon ,1 x Rose2-Oct-201-Oct-20Pumpkin Spice4
11Selection: 3 x Pistachio ,1 x Vanilla ,1 x Chocolate ,1 x Rose3-Oct-201-Oct-20Coffee4
12Selection: 3 x Rose ,3 x Lemon3-Oct-201-Oct-20Rose2
13Selection: 1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Strawberries & Cream ,1 x Raspberry ,1 x Nutella3-Oct-201-Oct-20Vanilla2
14Selection: 1 x Chocolate Chilli Mango ,1 x Salted Caramel ,1 x Pumpkin Spice ,1 x Chocolate Mint ,1 x Chocolate ,1 x Coffee3-Oct-202-Oct-20Rose2
152-Oct-20Pistachio3
162-Oct-20Coffee2
172-Oct-20Lemon2
182-Oct-20Chocolate1
192-Oct-20Nutella3
202-Oct-20Raspberry4
212-Oct-20Chocolate Mint6
222-Oct-20Vanilla4
232-Oct-20Pumpkin Spice9
242-Oct-20Salted Caramel5
252-Oct-20Earl Grey3
262-Oct-20Strawberries & Cream3
272-Oct-20Chocolate Chilli Mango7
283-Oct-20Pistachio3
293-Oct-20Vanilla1
303-Oct-20Chocolate2
313-Oct-20Rose4
323-Oct-20Lemon3
333-Oct-20Pumpkin Spice2
343-Oct-20Salted Caramel3
353-Oct-20Strawberries & Cream1
363-Oct-20Raspberry1
373-Oct-20Nutella1
383-Oct-20Chocolate Chilli Mango1
393-Oct-20Chocolate Mint1
403-Oct-20Coffee1
Sheet14


The code:

Power Query:
= Table.Group(Table.TransformColumns(Table.Combine(Table.ToList(Excel.CurrentWorkbook(){[Name="Table30"]}[Content],each Table.FromRows(List.Transform(Text.Split(Text.AfterDelimiter(_{0},": ")," ,"),(x)=>{_{1}}&Text.Split(x," x "))))),{"Column2",Number.From}),{"Column1","Column3"},{"Total",each List.Sum([Column2])})

1601889598332.png
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top