Find and count distinct text strings from row and return in one cell

[togin]

Hey Excel experts,

I need a formula to find and count distinct text values from a row and return the texts + count in one cell. Please see example.

Any ideers are appreciated.

Br
Tom

 

[dave3009]

Here's one suggestion.

Book1
	A	B	C	D	E	F
1	ID	Header1	Header2	Header3	Header4	Result
2	1	Dog	Dog	Cat	Cat	Dog (2),Cat (2)
3	2	Dog		Cat		Dog (1),Cat (1)
4	3	Dog	Ferret	Ferret	Dog	Dog (2),Ferret (2)
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=LET(uni,UNIQUE(FILTER(B2:E2,B2:E2<>""),1),TEXTJOIN({" ",","},1,TOROW(VSTACK(uni,"("&COUNTIFS(B2:E2,uni)&")"),1,1)))

 

[togin]

Here's one suggestion.

Book1
	A	B	C	D	E	F
1	ID	Header1	Header2	Header3	Header4	Result
2	1	Dog	Dog	Cat	Cat	Dog (2),Cat (2)
3	2	Dog		Cat		Dog (1),Cat (1)
4	3	Dog	Ferret	Ferret	Dog	Dog (2),Ferret (2)
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=LET(uni,UNIQUE(FILTER(B2:E2,B2:E2<>""),1),TEXTJOIN({" ",","},1,TOROW(VSTACK(uni,"("&COUNTIFS(B2:E2,uni)&")"),1,1)))

Thank you very much for the feedback

I am having a bit of trouble implementing it, however. The formula returns "#name?". What could I be doing wrong?

I'm using 365 if that matters.

 

[dave3009]

TOROW and VSTACK are only available in the M365 Office Inside Beta Channel.

 

[togin]

TOROW and VSTACK are only available in the M365 Office Inside Beta Channel.

Ahh ok.

Any chance you know how to achieve the same result with more "standard" formulas?

 

[Fluff]

TOROW and VSTACK are only available in the M365 Office Inside Beta Channel.

They have been released to the monthly channel with update 2208

 

[togin]

My company is doing delayed updates for M365 so we only got updated to 2208 this week. So now I have been able to use the formula, this has been most helpfull, thank you