Clever formula to concatenate

[ravikiran]

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.



Any helpful suggestions please.

Thanks.

 

[Hui]

Assuming this data layout:



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





Enjoy

 

[ravikiran]

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.

 

[rahulshewale1]

Hi @ravikiran ,

PFA Solution Using office 365. Using helper Column

 

[Peter Bartholomew]

An advantage of using Excel 365 is that the preparation of formulae can be very similar to writing a functional program.

= 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)

By the way, the word Section in the table is the result of number formatting to save typing