Removing TEXTJOIN duplicates with multiple rows

[Ben Taylor]

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

[#2]

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)

[#3 · Accepted solution]

Ben,

you need simply counting:

=IF(COUNTIFS($A$5:$A$8,$E5,$C$5:$C$8,G$1),"A","")

[#4]

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!

[#5]

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!