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

#### togin

##### New Member
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

#### Attachments

• Example.JPG
36 KB · Views: 4

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### dave3009

##### Well-known Member
Here's one suggestion.

Book1
ABCDEF
21DogDogCatCatDog (2),Cat (2)
32DogCatDog (1),Cat (1)
43DogFerretFerretDogDog (2),Ferret (2)
Sheet1
Cell Formulas
RangeFormula
F2:F4F2=LET(uni,UNIQUE(FILTER(B2:E2,B2:E2<>""),1),TEXTJOIN({" ",","},1,TOROW(VSTACK(uni,"("&COUNTIFS(B2:E2,uni)&")"),1,1)))

#### togin

##### New Member
Here's one suggestion.

Book1
ABCDEF
21DogDogCatCatDog (2),Cat (2)
32DogCatDog (1),Cat (1)
43DogFerretFerretDogDog (2),Ferret (2)
Sheet1
Cell Formulas
RangeFormula
F2:F4F2=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

##### Well-known Member
TOROW and VSTACK are only available in the M365 Office Inside Beta Channel.

#### togin

##### New Member
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

##### MrExcel MVP, Moderator
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

##### New Member
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

Replies
5
Views
174
Replies
5
Views
201
Replies
9
Views
90
Replies
15
Views
435
Replies
3
Views
64

1,186,505
Messages
5,958,239
Members
438,345
Latest member
batyr

### 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.

### Which adblocker are you using?

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

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