Count unique text strings in a column

[Darren Waters]

I'd appreciate some help.

I have a column of tags. Each cell can have multiple tags separated by commas. Each cell will have a unique set of tags within that cell, but tags can be repeated in other rows.

e.g.
Dog, Duck, Barn, Door
Dog, Cat, Cow
Door, Sheep, Goat
Barn, Farmer, Dog

How do I list out the unique tags only and then count how many of those unique tags are in the column?

Ideally what I'd like is the unique tags listed in one column with the count in the next column. e.g.

Dog 3
Duck 1
Barn 2
Door 2
Cat 1
Cow 1
Sheep 1
Goat 1
Farmer 1

thanks

[Fluff13]

How about

=LET(a,TRIM(TEXTSPLIT(TEXTJOIN(", ",,A2:A5),,",")),u,UNIQUE(a),HSTACK(u,BYROW(u,LAMBDA(br,SUM(--(br=a))))))

[Darren Waters]

Wow. Thank you Fluff13. That's amazing. Could I ask one more favour - would you mind walking through the formula and what the parts do? I'd massively appreciate that.

[Fluff13]

The textjoin joins all the rows with a comma in between & the textsplit then splits everything into a vertical array based on the commas.
The hstack then stacks the unique values in one column with the count in the other.

[HansDouwe]

Please try:

=LET(t,TEXTSPLIT(TEXTJOIN(", ",,A1:A4),,", "),u,UNIQUE(t),HSTACK(u,MAP(u,LAMBDA(c,SUM(N(c=t))))))

[Czeslaw]

Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source,"Column1",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),{"Column1.1", "Column1.2", "Column1.3", "Column1.4"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Text.Trim}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"Value"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"