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
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
514
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
514
Office Version
  1. 365
Platform
  1. Windows
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])})
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,971
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:

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,969

ADVERTISEMENT

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
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,971
Have you tried the macro I suggested in Post #13?
 

matthew.armitage

Board Regular
Joined
Nov 23, 2009
Messages
72

ADVERTISEMENT

The code in the Macros posts 13 and 15 worked also
 

matthew.armitage

Board Regular
Joined
Nov 23, 2009
Messages
72
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
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
514
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,525
Messages
5,572,641
Members
412,478
Latest member
MakeItWorkVBA
Top