Animal Count extracted from list in cells

[surkdidat]

Hi all

Is there a way I can extract and count the "Animals requested" in column B please.

I would just want a count of the animals (note the singular and plural spellings in the data field depending on number of animals)

so

7 blue dogs
8 orange cats
21 yellow horses
3 green rabbits
2 red dogs
4 black cats

	Column A	Column B	Column C
Row 1	ID Request	Animals requested	Date
Row 2	Req001	3x Blue dogs, 2x orange cats, 14x yellow horses	01/07/2022
Row 3	Req002	1x yellow horse, 2x green rabbits, 1x blue dog	04/07/2022
Row 4	Req003	2x orange cats	05/07/2022
Row 5	Req004	5x yellow horses	05/07/2022
Row 6	Req005	1x green rabbit, 1x yellow horse, 3x blue dogs, 4x orange cats	07/07/2022
Row 7	Req006	2x Red dogs, 4x black cats	07/07/2022

 

[lrobbo314]

Using Power Query.

Book3
	A	B	C	D	E	F
1	ID Request	Animals requested	Date		Index	Count
2	Req001	3x Blue dogs, 2x orange cats, 14x yellow horses	1/7/2022		Blue Dogs	7
3	Req002	1x yellow horse, 2x green rabbits, 1x blue dog	4/7/2022		Orange Cats	8
4	Req003	2x orange cats	5/7/2022		Yellow Horses	21
5	Req004	5x yellow horses	5/7/2022		Green Rabbits	3
6	Req005	1x green rabbit, 1x yellow horse, 3x blue dogs, 4x orange cats	7/7/2022		Red Dogs	2
7	Req006	2x Red dogs, 4x black cats	7/7/2022		Black Cats	4
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitRow = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Animals requested", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Animals requested"),
    SplitCol = Table.SplitColumn(SplitRow, "Animals requested", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Animals requested.1", "Animals requested.2"}),
    TN = Table.TransformColumns(SplitCol,{{"Animals requested.1", each Number.From(Text.Replace(_,"x",""))}}),
    Index = Table.AddIndexColumn(TN, "Index", 0, 1, Int64.Type),
    S = Table.TransformColumns(Index,{{"Index", each Text.Proper(if Index{_}[Animals requested.1] = 1 then Index{_}[Animals requested.2] & "s" else Index{_}[Animals requested.2])}}),
    Group = Table.Group(S, {"Index"}, {{"Count", each List.Sum([Animals requested.1]), type number}})
in
    Group

 

[surkdidat]

Thank you for the above. Power BI is something I am completely unfamiliar with. If I had an additional column (say Column E) that had a country in, UK, France, Germany etc, and I only wanted it on the "UK", how would I add this to the above please? (Apologies!)

Column A	Column B	Column C	Column D	Column E
Row 1	ID Request	Animals requested	Date	Country
Row 2	Req001	3x Blue dogs, 2x orange cats, 14x yellow horses	01/07/2022	UK
Row 3	Req002	1x yellow horse, 2x green rabbits, 1x blue dog	04/07/2022	UK
Row 4	Req003	2x orange cats	05/07/2022	Germany
Row 5	Req004	5x yellow horses	05/07/2022	France
Row 6	Req005	1x green rabbit, 1x yellow horse, 3x blue dogs, 4x orange cats	07/07/2022	UK
Row 7	Req006	2x Red dogs, 4x black cats	07/07/2022	France

 

[lrobbo314]

This should do it.

Book3 (version 2).xlsb
	A	B	C	D	E	F	G
1	ID Request	Animals requested	Date	Country		Index	Count
2	Req001	3x Blue dogs, 2x orange cats, 14x yellow horses	1/7/2022	UK		Blue Dogs	7
3	Req002	1x yellow horse, 2x green rabbits, 1x blue dog	4/7/2022	UK		Orange Cats	6
4	Req003	2x orange cats	5/7/2022	Germany		Yellow Horses	16
5	Req004	5x yellow horses	5/7/2022	France		Green Rabbits	3
6	Req005	1x green rabbit, 1x yellow horse, 3x blue dogs, 4x orange cats	7/7/2022	UK
7	Req006	2x Red dogs, 4x black cats	7/7/2022	France
Sheet5

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Filter = Table.SelectRows(Source, each ([Country] = "UK")),
    SplitRow = Table.ExpandListColumn(Table.TransformColumns(Filter, {{"Animals requested", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Animals requested"),
    SplitCol = Table.SplitColumn(SplitRow, "Animals requested", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Animals requested.1", "Animals requested.2"}),
    TN = Table.TransformColumns(SplitCol,{{"Animals requested.1", each Number.From(Text.Replace(_,"x",""))}}),
    Index = Table.AddIndexColumn(TN, "Index", 0, 1, Int64.Type),
    S = Table.TransformColumns(Index,{{"Index", each Text.Proper(if Index{_}[Animals requested.1] = 1 then Index{_}[Animals requested.2] & "s" else Index{_}[Animals requested.2])}}),
    Group = Table.Group(S, {"Index"}, {{"Count", each List.Sum([Animals requested.1]), type number}})
in
    Group

 

[surkdidat]

Thank you for your help! Now need to give myself a quick crash course on how PowerBI works on YouTube . Appreciate you help

This should do it.

Book3 (version 2).xlsb
	A	B	C	D	E	F	G
1	ID Request	Animals requested	Date	Country		Index	Count
2	Req001	3x Blue dogs, 2x orange cats, 14x yellow horses	1/7/2022	UK		Blue Dogs	7
3	Req002	1x yellow horse, 2x green rabbits, 1x blue dog	4/7/2022	UK		Orange Cats	6
4	Req003	2x orange cats	5/7/2022	Germany		Yellow Horses	16
5	Req004	5x yellow horses	5/7/2022	France		Green Rabbits	3
6	Req005	1x green rabbit, 1x yellow horse, 3x blue dogs, 4x orange cats	7/7/2022	UK
7	Req006	2x Red dogs, 4x black cats	7/7/2022	France
Sheet5

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    Filter = Table.SelectRows(Source, each ([Country] = "UK")),
    SplitRow = Table.ExpandListColumn(Table.TransformColumns(Filter, {{"Animals requested", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Animals requested"),
    SplitCol = Table.SplitColumn(SplitRow, "Animals requested", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Animals requested.1", "Animals requested.2"}),
    TN = Table.TransformColumns(SplitCol,{{"Animals requested.1", each Number.From(Text.Replace(_,"x",""))}}),
    Index = Table.AddIndexColumn(TN, "Index", 0, 1, Int64.Type),
    S = Table.TransformColumns(Index,{{"Index", each Text.Proper(if Index{_}[Animals requested.1] = 1 then Index{_}[Animals requested.2] & "s" else Index{_}[Animals requested.2])}}),
    Group = Table.Group(S, {"Index"}, {{"Count", each List.Sum([Animals requested.1]), type number}})
in
    Group

 

[rlv01]

A VBA approach.

Book2
	A	B	C	D	E	F	G	H
1	ID Request	Animals requested	Date	Country		Index	Count	Filter
2	Req001	3x Blue dogs, 2x orange cats, 14x yellow horses	1/7/2022	UK		Blue Dogs	7	UK
3	Req002	1x yellow horse, 2x green rabbits, 1x blue dog	4/7/2022	UK		Orange Cats	6	UK
4	Req003	2x orange cats	5/7/2022	Germany		Yellow Horses	16	UK
5	Req004	5x yellow horses	5/7/2022	France		Green Rabbits	3	UK
6	Req005	1x green rabbit, 1x yellow horse, 3x blue dogs, 4x orange cats	7/7/2022	UK
7	Req006	2x Red dogs, 4x black cats	7/7/2022	France
Sheet1

Sub CountAnimalsInColumnB()
    Dim Rng As Range, R As Range
    Dim S As Variant, SA As Variant, SB As Variant, Key As Variant
    Dim Txt As String, KeyStr As String, FilterStr
    Dim I As Long, RefNum As Long
    Dim SD As Object

    FilterStr = "UK"  '"Germany" "France" "*"
    
    With ActiveSheet
        Set Rng = .Range("B2", .Range("B" & .Rows.Count).End(xlUp))
        .AutoFilterMode = False
        Rng.Offset(0, 4).Resize(, 3).ClearContents
        Rng.Offset(0, 2).AutoFilter Field:=1, Criteria1:=FilterStr
    End With
   
    Set SD = CreateObject("Scripting.dictionary")
   
    With CreateObject("VBScript.RegExp")
        .Global = True
        For Each R In Rng.SpecialCells(xlCellTypeVisible)
            Txt = Application.Trim(R.Value)
            .Pattern = "[^,0-9]"
            SA = Split(.Replace(Txt, ""), ",")
           
            .Pattern = "[^, a-zA-Z]"
            SB = Split(.Replace(VBA.Replace(Txt, "x ", ""), ""), ",")
           
            For I = LBound(SA) To UBound(SA)
                KeyStr = Application.WorksheetFunction.Proper(Trim(SB(I)))
                If Right(KeyStr, 1) <> "s" Then
                    KeyStr = KeyStr & "s"
                End If
                RefNum = Val((Trim(SA(I))))
               
                If Not SD.exists(KeyStr) Then
                    SD.Add KeyStr, RefNum
                    Else    'It's a duplicate key
                        RefNum = RefNum + SD.Item(KeyStr)
                        SD.Item(KeyStr) = RefNum
                    End If
                Next I
            Next R
        End With
       
       ActiveSheet.AutoFilterMode = False
        Set Rng = ActiveSheet.Range("F2")
        I = 0
        For Each Key In SD.Keys
            S = S & "  " & Key & ": " & SD.Item(Key) & vbCr
            Rng.Offset(I, 0).Value = Key
            Rng.Offset(I, 1).Value = SD.Item(Key)
            Rng.Offset(I, 2).Value = FilterStr
            I = I + 1
        Next Key
End Sub