Calculation from text string

[matthew.armitage]

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]

Please see below steps:

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

 

[shaowu459]

Remember to change the Table name Table34 to the name of your table.

= 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]

Try:

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.

 

[matthew.armitage]

Thank you!

 

[Anthony47]

A possible solution via macro:

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]

Have you tried the macro I suggested in Post #13?

 

[matthew.armitage]

The code in the Macros posts 13 and 15 worked also

 

[mumps]

Glad it worked out.

 

[matthew.armitage]

Remember to change the Table name Table34 to the name of your table.

= 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
	A	B
1	Column1	Column2
2	Line Item Properties	Date
3	Selection: 3 x Chocolate ,3 x Raspberry ,3 x Pistachio ,3 x Lemon ,3 x Nutella ,3 x Chocolate Mint	01-Oct-2020
4	Selection: 6 x Chocolate Mint	01-Oct-2020
5	Selection: 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 Nutella	01-Oct-2020
6	Selection: 2 x Pumpkin Spice ,2 x Raspberry ,2 x Lemon	01-Oct-2020
7	Selection: 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 Mango	01-Oct-2020
8	Selection: 1 x Strawberries & Cream ,1 x Vanilla ,1 x Raspberry ,1 x Nutella ,1 x Salted Caramel ,1 x Chocolate Mint	01-Oct-2020
9	Selection: 3 x Pumpkin Spice ,3 x Chocolate Chilli Mango	01-Oct-2020
10	Selection: 3 x Pumpkin Spice ,2 x Chocolate Mint ,1 x Salted Caramel	01-Oct-2020
11	Selection: 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 Rose	01-Oct-2020
12	Selection: 3 x Pistachio ,1 x Vanilla ,1 x Chocolate ,1 x Rose	01-Oct-2020
13	Selection: 3 x Rose ,3 x Lemon	01-Oct-2020
14	Selection: 1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Strawberries & Cream ,1 x Raspberry ,1 x Nutella	01-Oct-2020
15	Selection: 1 x Chocolate Chilli Mango ,1 x Salted Caramel ,1 x Pumpkin Spice ,1 x Chocolate Mint ,1 x Chocolate ,1 x Coffee	01-Oct-2020
Sheet1

 

[shaowu459]

Since your data has column headers, so check the checkbox to make the first line as headers.

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

Book1.xlsx
	A	B	C	D	E	F
1	Line Item Properties	Date		Column1	Column3	Total
2	Selection: 3 x Chocolate ,3 x Raspberry ,3 x Pistachio ,3 x Lemon ,3 x Nutella ,3 x Chocolate Mint	1-Oct-20		1-Oct-20	Chocolate	3
3	Selection: 6 x Chocolate Mint	1-Oct-20		1-Oct-20	Raspberry	5
4	Selection: 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 Nutella	1-Oct-20		1-Oct-20	Pistachio	3
5	Selection: 2 x Pumpkin Spice ,2 x Raspberry ,2 x Lemon	1-Oct-20		1-Oct-20	Lemon	7
6	Selection: 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 Mango	2-Oct-20		1-Oct-20	Nutella	5
7	Selection: 1 x Strawberries & Cream ,1 x Vanilla ,1 x Raspberry ,1 x Nutella ,1 x Salted Caramel ,1 x Chocolate Mint	2-Oct-20		1-Oct-20	Chocolate Mint	9
8	Selection: 3 x Pumpkin Spice ,3 x Chocolate Chilli Mango	2-Oct-20		1-Oct-20	Strawberries & Cream	2
9	Selection: 3 x Pumpkin Spice ,2 x Chocolate Mint ,1 x Salted Caramel	2-Oct-20		1-Oct-20	Salted Caramel	2
10	Selection: 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 Rose	2-Oct-20		1-Oct-20	Pumpkin Spice	4
11	Selection: 3 x Pistachio ,1 x Vanilla ,1 x Chocolate ,1 x Rose	3-Oct-20		1-Oct-20	Coffee	4
12	Selection: 3 x Rose ,3 x Lemon	3-Oct-20		1-Oct-20	Rose	2
13	Selection: 1 x Pumpkin Spice ,2 x Salted Caramel ,1 x Strawberries & Cream ,1 x Raspberry ,1 x Nutella	3-Oct-20		1-Oct-20	Vanilla	2
14	Selection: 1 x Chocolate Chilli Mango ,1 x Salted Caramel ,1 x Pumpkin Spice ,1 x Chocolate Mint ,1 x Chocolate ,1 x Coffee	3-Oct-20		2-Oct-20	Rose	2
15				2-Oct-20	Pistachio	3
16				2-Oct-20	Coffee	2
17				2-Oct-20	Lemon	2
18				2-Oct-20	Chocolate	1
19				2-Oct-20	Nutella	3
20				2-Oct-20	Raspberry	4
21				2-Oct-20	Chocolate Mint	6
22				2-Oct-20	Vanilla	4
23				2-Oct-20	Pumpkin Spice	9
24				2-Oct-20	Salted Caramel	5
25				2-Oct-20	Earl Grey	3
26				2-Oct-20	Strawberries & Cream	3
27				2-Oct-20	Chocolate Chilli Mango	7
28				3-Oct-20	Pistachio	3
29				3-Oct-20	Vanilla	1
30				3-Oct-20	Chocolate	2
31				3-Oct-20	Rose	4
32				3-Oct-20	Lemon	3
33				3-Oct-20	Pumpkin Spice	2
34				3-Oct-20	Salted Caramel	3
35				3-Oct-20	Strawberries & Cream	1
36				3-Oct-20	Raspberry	1
37				3-Oct-20	Nutella	1
38				3-Oct-20	Chocolate Chilli Mango	1
39				3-Oct-20	Chocolate Mint	1
40				3-Oct-20	Coffee	1
Sheet14

The code:

= 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])})