• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Clever formula to concatenate

ravikiran

Member
Hi Gurus,

I need a clever formula to concatenate few rows. Example below. Depending on the active sections the output in the highlighted cell changes. In the following example it is expected to be Sections 1, 2 and 4. However if 2 3 and 4 are active, the output will be Sections 2, 3 and 4. Also if only One section is active, I would like the output to be Section instead of plural.

73335

Any helpful suggestions please.

Thanks.
 

Hui

Excel Ninja
Staff member
Assuming this data layout:

73337

B9: ="Section" & IF(C9>1,"s","") & " " & TEXTJOIN(" ",TRUE, IF(C3:C6="Yes", RIGHT(B3:B6,1),"")) Ctrl+Shift+Enter
C9: =COUNTIF(C3:C6,"Yes")

73338

73339

Enjoy
 

ravikiran

Member
Thanks Hui. I am able achieve this slightly differently.

My issue is when I try to do the following:
  1. Concatenate "and" before the last section number . e.g. Sections 1, 2 and 3 or Sections 2, 4 and 3
  2. Also add "commas" where relevant. e.g. Sections 1, 2, 3 and 4
Output of this formula feeds into very important documents and having that detail is quite important. Trying to find out if there is a cheeky way to achieve this using a formula. If not I will be forced to use VBA.

Thanks again for your help with this.
 

Attachments

Peter Bartholomew

Well-Known Member
An advantage of using Excel 365 is that the preparation of formulae can be very similar to writing a functional program.
Code:
= LET(
  activeSections, FILTER(Section, Active="Yes", "None"),
  n, COUNTA(activeSections),
  k, SEQUENCE(n,1),
  Prefix, SWITCH(k, 1, IF(n=1,"Section ","Sections "), n, " and ", ", "),
  output, CONCAT(Prefix & activeSections),
  output)
73358
By the way, the word Section in the table is the result of number formatting to save typing
 
Top