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

togin

New Member
Joined
Sep 11, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
    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
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
Here's one suggestion.

Book1
ABCDEF
1IDHeader1Header2Header3Header4Result
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)))
 
Upvote 0
Solution

togin

New Member
Joined
Sep 11, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Here's one suggestion.

Book1
ABCDEF
1IDHeader1Header2Header3Header4Result
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.
 
Upvote 0

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
TOROW and VSTACK are only available in the M365 Office Inside Beta Channel.
 
Upvote 0

togin

New Member
Joined
Sep 11, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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?
 
Upvote 0

togin

New Member
Joined
Sep 11, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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 :)
 
Upvote 0

Forum statistics

Threads
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.
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
Top