Forum Discussion

Ben Taylor's avatar
Ben Taylor
Copper Contributor
Jul 06, 2018

Removing TEXTJOIN duplicates with multiple rows

Hi all,

 

Attached is a spreadsheet designed to track the start dates of certain modules which happen in different terms, converting a table of this information into a calendar.

 

The formula detects whether the date and term in the calendar match the date and term listed in the table and, if they do, marks the corresponding cell with an "A". To get this text output, I had to use TEXTJOIN, but this resulted in "A A" where two different modules have the same term and start date.

 

To remove duplicates from the TEXTJOIN, I used the handy formulae listed here: Removing duplicates when using TEXTJOIN.

 

This seems to work well except in one case: if a module at the top of the list has the same date but a different term to one at the bottom of the list, "A" only appears in the calendar row corresponding to the module at the top of the list.

 

I've illustrated the desired output in the attachment. Is this a problem with the order in which the nested IFs evaluate? I'm quite new to arrays so don't fully understand how they evaluate either (does the formula cycle through each array item in sequence, or does the outer IF cycle through its input arrays first, then the inner IF?)... A revised formula (and any explanation) would be much appreciated!

 

Thanks very much for your help,

 

Ben

Formulas & Functions
TEXTJOIN
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Jul 06, 2018

    Ben,

     

    you need simply counting:

    =IF(COUNTIFS($A$5:$A$8,$E5,$C$5:$C$8,G$1),"A","")
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jul 06, 2018

    Hi Ben,

     

    If I understood your logic correctly you'd like to have instead of text like "A A" just "A" and variants of your texts are empty one; single "A" or repeated "A" with spaces.

     

    When you may use something like

    =LEFT(TEXTJOIN(" ", TRUE, IF(<condition>, "A","")),1)
    • Ben Taylor's avatar
      Ben Taylor
      Copper Contributor
      Jul 06, 2018

      Hi Sergei,

       

      This is a really neat little solution which works - thank you!

       

      I changed this formula:

      {=TEXTJOIN(" ",TRUE, IF(MATCH($C$5:$C$8,$C$5:$C$8,0)=(ROW($C$5:$C$8)-ROW($C$5)+1), IF(($A$5$A$8=$E6)*($C$5:$C$8=K$1), "A", ""), ""))}

      to this one:

      {=LEFT(TEXTJOIN(" ",TRUE, IF(($A$5:$A$8=$E6)*($C$5:$C$8=K$1), "A", "")), 1)}

       

      If I were to stop here, I would use this answer. I intend, however, to add another column of "B" dates which can overlap the "A" dates, so some cells would need to read "A B" (but not "A A B").

       

      In any case, thanks very much for the swift response and nifty solution, Sergei!

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Jul 06, 2018

    Ben,

     

    you need simply counting:

    =IF(COUNTIFS($A$5:$A$8,$E5,$C$5:$C$8,G$1),"A","")
    • Ben Taylor's avatar
      Ben Taylor
      Copper Contributor
      Jul 06, 2018

      Hi Detlef,

       

      Thank you very much for your answer - it works very well and is a far simpler solution than my own formulae!

       

      I shall combine this with the IFS function to expand the formula to cover not just "A" dates but extra columns of "B" dates, "C" dates etc.

       

      Thanks again for your swift response, Detlef!