Forum Discussion

renee_crozier's avatar
renee_crozier
Copper Contributor
Apr 17, 2024

Removing Duplicates When Using XLOOKUPs, IF, and TEXTJOIN

I am trying to compile a list of people from a different sheet named "Asset" that are associated with a certain asset in Excel (Office 365). Using this formula, I want to return a name if the asset was modified less than 6 months ago, return a name if the asset was created less than 6 months ago, and then the owner of the asset. Since these can be the same person, I am looking to reduce the multiple names that are returned.

=TEXTJOIN("; ", TRUE, IF(XLOOKUP(E15, Asset_Path, Asset_ModifiedDate)>TODAY()-184, XLOOKUP(E15, Asset_Path, Asset_ModifiedBy), "Mod > 6 months"), IF(XLOOKUP(E15, Asset_Path, Asset_CreatedDate)>TODAY()-184, XLOOKUP(E15, Asset_Path, Asset_CreatedBy), "Created > 6 months"), XLOOKUP(E15, Asset_Path, Asset_Owner))

Asset_Path, Asset_ModifiedDate, Asset_ModifiedBy, Asset_CreatedDate, Asset_CreatedBy, Asset_Owner are all named ranges.

 

Sample source data I'm working with:

Path

ContentOwner

Created

CreatedBy

ExpirationDate

LinkURL

Modified

ModifiedBy

Title

/sites/us/Assets0001/cat-in-the-hat.pdf

Jill Denvers

9/3/2021

Dan Smith

11/28/2023

https://website.com/sites/us/Assets0001/cat-in-the-hat.pdf

11/28/2023

Alex White

Cat in the Hat

/sites/us/Assets0001/the-hobbit.pdf

John Thomas

12/11/2024

Jessica Samson

2/16/2024

https://website.com/sites/us/Assets0001/the-hobbit.pdf

1/16/2024

Greg Jones

The Hobbit

/sites/us/Assets0001/the-art-of-war.pdf

John Doe

4/15/2022

Rachel Richards

4/15/2025

https://website.com/sites/us/Assets0001/the-art-of-war.pdf

3/27/2024

John Doe

The Art of War

 

Current result with the above formula:

LinkURLTitleAsset Contributor
/sites/us/Assets0001/the-art-of-war.pdfThe Art of WarJohn Doe; Created > 6 months; John Doe

 

Result I am looking to achieve:

LinkURLTitleAsset Contributor
/sites/us/Assets0001/the-art-of-war.pdfThe Art of WarJohn Doe; Created > 6 months;

 

I've tried removing the TEXTJOIN and adding &"; "& between each IF statement, using CONCAT, and adding UNIQUE before TEXTJOIN but the duplicate names kept appearing.

  • renee_crozier 

    Try this.

    =TEXTJOIN("; ",TRUE,
    UNIQUE(VSTACK(
    IF(XLOOKUP(A15,Tabelle1[Path],Tabelle1[Modified])>TODAY()-184,XLOOKUP(A15,Tabelle1[Path],Tabelle1[ModifiedBy]),"Mod > 6 months"),
    IF(XLOOKUP(A15,Tabelle1[Path],Tabelle1[Created])>TODAY()-184,XLOOKUP(A15,Tabelle1[Path],Tabelle1[CreatedBy]),"Created > 6 months"),
    XLOOKUP(A15,Tabelle1[Path],Tabelle1[ContentOwner]))))
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor

    renee_crozier 

    Try this.

    =TEXTJOIN("; ",TRUE,
    UNIQUE(VSTACK(
    IF(XLOOKUP(A15,Tabelle1[Path],Tabelle1[Modified])>TODAY()-184,XLOOKUP(A15,Tabelle1[Path],Tabelle1[ModifiedBy]),"Mod > 6 months"),
    IF(XLOOKUP(A15,Tabelle1[Path],Tabelle1[Created])>TODAY()-184,XLOOKUP(A15,Tabelle1[Path],Tabelle1[CreatedBy]),"Created > 6 months"),
    XLOOKUP(A15,Tabelle1[Path],Tabelle1[ContentOwner]))))
    • renee_crozier's avatar
      renee_crozier
      Copper Contributor
      Thanks! That worked! I didn't know about the VSTACK function.