Forum Discussion

Deleted's avatar
Deleted
Jun 30, 2017

IFS statement using concat

I have a sheet I am trying to combine values into one cell based on 2 cells.

For example:

Member 1    Drug1        Flag1

Member1     Drug2        Flag1

Member1     Drug3        Flag1

Member2     Drug1        Flag1

Member2     Drug1        Flag2

Member2     Drug2        Flag2

 

I am trying to say if Member 1=Member1 and Flag1=Flag1 then concatenate the drugs for those flags into a column.  

Ideally I would like to have

Member1       Drug1, Drug2, Drug3         Flag1

Does anyone have any ideas on how to present this?

Thanks so much!

 

Formulas & Functions
Need Help
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jun 30, 2017

    Hi Shel,

     

    Here is the solution using TEXTJOIN() function built-in in Excel 2016 for Office365 subscribers. As I know so far it's not available for other users. However, in general better to use Get & Transform (aka Power Query) for such kind of transformations, available for all versions of Excel starting from 2013.

     

    Okay, assuming your data located as here

     

    enter following array formulas (i.e. using Ctrl+Shift+Enter)

    in E2 (returns Member for unique combination of Member and Flag)

     

    =IFERROR(
   INDEX(A$2:A$300,
      SMALL(
         IF(COUNTIFS(
               OFFSET($A$2:$A$300,,,ROW(A$2:A$300)-ROW(A$2)+1),$A$2:$A$300,
               OFFSET($C$2:$C$300,,,ROW($C$2:$C$300)-ROW($C$2)+1),$C$2:$C$300
            )=1,
            ROW(A$2:A$300)-ROW(A$2)+1
         ),ROWS($1:1)
      )
   ),""
)

    in G2 (same as above but returns Flag)

     

     

    =IFERROR(
   INDEX(C$2:C$300,
      SMALL(
         IF(COUNTIFS(
               OFFSET($A$2:$A$300,,,ROW(A$2:A$300)-ROW(A$2)+1),$A$2:$A$300,
               OFFSET($C$2:$C$300,,,ROW($C$2:$C$300)-ROW($C$2)+1),$C$2:$C$300
            )=1,
            ROW(A$2:A$300)-ROW(A$2)+1
         ),ROWS($1:1)
      )
   ),""
)

    After that in F2

     

     

    =TEXTJOIN(
   ", ",
   TRUE,
   IF($A$2:$A$7=E2,
      IF($C$2:$C$7=G2,$B$2:$B$7,""),
      ""
   )
)

    which concatenates all drugs from column B for combination of member and flag as in E2 and G2.

     

    After that select E2:G2 and drug selection down till first empty record appears (other words copy them down).

     

Resources