Animal Count extracted from list in cells

S

surkdidat

Well-known Member
Joined
Oct 1, 2011
Messages
597
Office Version
  1. 365
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 AColumn BColumn C
Row 1ID RequestAnimals requestedDate
Row 2Req0013x Blue dogs, 2x orange cats, 14x yellow horses01/07/2022
Row 3Req0021x yellow horse, 2x green rabbits, 1x blue dog04/07/2022
Row 4Req0032x orange cats05/07/2022
Row 5Req0045x yellow horses05/07/2022
Row 6Req0051x green rabbit, 1x yellow horse, 3x blue dogs, 4x orange cats07/07/2022
Row 7Req0062x Red dogs, 4x black cats07/07/2022
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Using Power Query.

Book3
ABCDEF
1ID RequestAnimals requestedDateIndexCount
2Req0013x Blue dogs, 2x orange cats, 14x yellow horses1/7/2022Blue Dogs7
3Req0021x yellow horse, 2x green rabbits, 1x blue dog4/7/2022Orange Cats8
4Req0032x orange cats5/7/2022Yellow Horses21
5Req0045x yellow horses5/7/2022Green Rabbits3
6Req0051x green rabbit, 1x yellow horse, 3x blue dogs, 4x orange cats7/7/2022Red Dogs2
7Req0062x Red dogs, 4x black cats7/7/2022Black Cats4
Sheet3


Power Query: 
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
 
Upvote 0
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 BColumn CColumn DColumn E
Row 1ID RequestAnimals requestedDateCountry
Row 2Req0013x Blue dogs, 2x orange cats, 14x yellow horses01/07/2022UK
Row 3Req0021x yellow horse, 2x green rabbits, 1x blue dog04/07/2022UK
Row 4Req0032x orange cats05/07/2022Germany
Row 5Req0045x yellow horses05/07/2022France
Row 6Req0051x green rabbit, 1x yellow horse, 3x blue dogs, 4x orange cats07/07/2022UK
Row 7Req0062x Red dogs, 4x black cats07/07/2022France
 
Upvote 0
This should do it.

Book3 (version 2).xlsb
ABCDEFG
1ID RequestAnimals requestedDateCountryIndexCount
2Req0013x Blue dogs, 2x orange cats, 14x yellow horses1/7/2022UKBlue Dogs7
3Req0021x yellow horse, 2x green rabbits, 1x blue dog4/7/2022UKOrange Cats6
4Req0032x orange cats5/7/2022GermanyYellow Horses16
5Req0045x yellow horses5/7/2022FranceGreen Rabbits3
6Req0051x green rabbit, 1x yellow horse, 3x blue dogs, 4x orange cats7/7/2022UK
7Req0062x Red dogs, 4x black cats7/7/2022France
Sheet5


Power Query: 
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
 
Upvote 0
Solution
Thank you for your help! Now need to give myself a quick crash course on how PowerBI works on YouTube . Appreciate you help :)

lrobbo314 said:
This should do it.

Book3 (version 2).xlsb
ABCDEFG
1ID RequestAnimals requestedDateCountryIndexCount
2Req0013x Blue dogs, 2x orange cats, 14x yellow horses1/7/2022UKBlue Dogs7
3Req0021x yellow horse, 2x green rabbits, 1x blue dog4/7/2022UKOrange Cats6
4Req0032x orange cats5/7/2022GermanyYellow Horses16
5Req0045x yellow horses5/7/2022FranceGreen Rabbits3
6Req0051x green rabbit, 1x yellow horse, 3x blue dogs, 4x orange cats7/7/2022UK
7Req0062x Red dogs, 4x black cats7/7/2022France
Sheet5


Power Query: 
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
Click to expand...
 
Upvote 0
A VBA approach.
Book2
ABCDEFGH
1ID RequestAnimals requestedDateCountryIndexCountFilter
2Req0013x Blue dogs, 2x orange cats, 14x yellow horses1/7/2022UKBlue Dogs7UK
3Req0021x yellow horse, 2x green rabbits, 1x blue dog4/7/2022UKOrange Cats6UK
4Req0032x orange cats5/7/2022GermanyYellow Horses16UK
5Req0045x yellow horses5/7/2022FranceGreen Rabbits3UK
6Req0051x green rabbit, 1x yellow horse, 3x blue dogs, 4x orange cats7/7/2022UK
7Req0062x Red dogs, 4x black cats7/7/2022France
Sheet1


VBA Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Extract numeric value from cell equivalent to COlumn header
Replies
6
Views
592
Fluff
Fluff
F
  • Question Question
Custom search bar - Creating a front end for My Sold Research Tool.
Replies
0
Views
234
frewert
F
J
  • Locked
  • Question Question
Combining Cells/Rows and Adding values
Replies
1
Views
462
Fluff
Fluff
F
  • Question Question
comparing 3 ranges and return values that are missing based on 2 criteria
Replies
3
Views
460
Fluff
Fluff
G
  • Question Question
Should I use an Index or Vlookup?
Replies
2
Views
433
paleolith
P

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

Share this page

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