SOLVED
Home

Removing TEXTJOIN duplicates with multiple rows

%3CLINGO-SUB%20id%3D%22lingo-sub-212743%22%20slang%3D%22en-US%22%3ERemoving%20TEXTJOIN%20duplicates%20with%20multiple%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-212743%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20a%20spreadsheet%20designed%20to%20track%20the%20start%20dates%20of%20certain%20modules%20which%20happen%20in%20different%20terms%2C%20converting%20a%20table%20of%20this%20information%20into%20a%20calendar.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20detects%20whether%20the%20date%20and%20term%20in%20the%20calendar%20match%20the%20date%20and%20term%20listed%20in%20the%20table%20and%2C%20if%20they%20do%2C%20marks%20the%20corresponding%20cell%20with%20an%20%22A%22.%20To%20get%20this%20text%20output%2C%20I%20had%20to%20use%20TEXTJOIN%2C%20but%20this%20resulted%20in%20%22A%20A%22%20where%20two%20different%20modules%20have%20the%20same%20term%20and%20start%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20remove%20duplicates%20from%20the%20TEXTJOIN%2C%20I%20used%20the%20handy%20formulae%20listed%20here%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FFormulas-and-Functions%2FRemoving-duplicates-when-using-TEXTJOIN%2Fm-p%2F188950%23M5211%22%20target%3D%22_blank%22%3ERemoving%20duplicates%20when%20using%20TEXTJOIN%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20seems%20to%20work%20well%20except%20in%20one%20case%3A%20if%20a%20module%20at%20the%20top%20of%20the%20list%20has%20the%20same%20date%20but%20a%20different%20term%20to%20one%20at%20the%20bottom%20of%20the%20list%2C%20%22A%22%20only%20appears%20in%20the%20calendar%20row%20corresponding%20to%20the%20module%20at%20the%20top%20of%20the%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20illustrated%20the%20desired%20output%20in%20the%20attachment.%20Is%20this%20a%20problem%20with%20the%20order%20in%20which%20the%20nested%20IFs%20evaluate%3F%20I'm%20quite%20new%20to%20arrays%20so%20don't%20fully%20understand%20how%20they%20evaluate%20either%20(does%20the%20formula%20cycle%20through%20each%20array%20item%20in%20sequence%2C%20or%20does%20the%20outer%20IF%20cycle%20through%20its%20input%20arrays%20first%2C%20then%20the%20inner%20IF%3F)...%20A%20revised%20formula%20(and%20any%20explanation)%20would%20be%20much%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20very%20much%20for%20your%20help%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-212743%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETEXTJOIN%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-212856%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20TEXTJOIN%20duplicates%20with%20multiple%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-212856%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Detlef%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20answer%20-%20it%20works%20very%20well%20and%20is%20a%20far%20simpler%20solution%20than%20my%20own%20formulae!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20shall%20combine%20this%20with%20the%20IFS%20function%20to%20expand%20the%20formula%20to%20cover%20not%20just%20%22A%22%20dates%20but%20extra%20columns%20of%20%22B%22%20dates%2C%20%22C%22%20dates%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%20for%20your%20swift%20response%2C%20Detlef!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-212855%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20TEXTJOIN%20duplicates%20with%20multiple%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-212855%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20really%20neat%20little%20solution%20which%20works%20-%20thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20changed%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%7B%3DTEXTJOIN(%22%20%22%2CTRUE%2C%20IF(MATCH(%24C%245%3A%24C%248%2C%24C%245%3A%24C%248%2C0)%3D(ROW(%24C%245%3A%24C%248)-ROW(%24C%245)%2B1)%2C%20IF((%24A%245%24A%248%3D%24E6)*(%24C%245%3A%24C%248%3DK%241)%2C%20%22A%22%2C%20%22%22)%2C%20%22%22))%7D%3C%2FPRE%3E%3CP%3Eto%20this%20one%3A%3C%2FP%3E%3CPRE%3E%7B%3DLEFT(TEXTJOIN(%22%20%22%2CTRUE%2C%20IF((%24A%245%3A%24A%248%3D%24E6)*(%24C%245%3A%24C%248%3DK%241)%2C%20%22A%22%2C%20%22%22))%2C%201)%7D%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20were%20to%20stop%20here%2C%20I%20would%20use%20this%20answer.%20I%20intend%2C%20however%2C%20to%20add%20another%20column%20of%20%22B%22%20dates%20which%20can%20overlap%20the%20%22A%22%20dates%2C%20so%20some%20cells%20would%20need%20to%20read%20%22A%20B%22%20(but%20not%20%22A%20A%20B%22).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20any%20case%2C%20thanks%20very%20much%20for%20the%20swift%20response%20and%20nifty%20solution%2C%20Sergei!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-212756%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20TEXTJOIN%20duplicates%20with%20multiple%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-212756%22%20slang%3D%22en-US%22%3E%3CP%3EBen%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eyou%20need%20simply%20counting%3A%3C%2FP%3E%3CPRE%3E%3DIF(COUNTIFS(%24A%245%3A%24A%248%2C%24E5%2C%24C%245%3A%24C%248%2CG%241)%2C%22A%22%2C%22%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-212749%22%20slang%3D%22en-US%22%3ERe%3A%20Removing%20TEXTJOIN%20duplicates%20with%20multiple%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-212749%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Ben%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20I%20understood%20your%20logic%20correctly%20you'd%20like%20to%20have%20instead%20of%20text%20like%20%22A%20A%22%20just%20%22A%22%20and%20variants%20of%20your%20texts%20are%20empty%20one%3B%20single%20%22A%22%20or%20repeated%20%22A%22%20with%20spaces.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20you%20may%20use%20something%20like%3C%2FP%3E%0A%3CPRE%3E%3DLEFT(TEXTJOIN(%22%20%22%2C%20TRUE%2C%20IF(%26lt%3Bcondition%26gt%3B%2C%20%22A%22%2C%22%22))%2C1)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
Ben Taylor
New Contributor

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

4 Replies
Highlighted

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)
Highlighted
Solution

Ben,

 

you need simply counting:

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

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!

Highlighted

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!

Related Conversations
Issue with Textjoin, multiple criteria. IF/AND function
MrRAMMount in Excel on
13 Replies
How to SUM Data from non-regular rows
Andy_Przybysz in Excel on
1 Replies
Taking values from certain rows based on data input
Drakerla in Excel on
5 Replies