Apr 17 2024 10:19 AM - edited Apr 17 2024 10:23 AM
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 | 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 | 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 | 3/27/2024 | John Doe | The Art of War |
Current result with the above formula:
LinkURL | Title | Asset Contributor |
/sites/us/Assets0001/the-art-of-war.pdf | The Art of War | John Doe; Created > 6 months; John Doe |
Result I am looking to achieve:
LinkURL | Title | Asset Contributor |
/sites/us/Assets0001/the-art-of-war.pdf | The Art of War | John 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.
Apr 17 2024 10:53 AM
SolutionTry 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]))))
Apr 17 2024 11:23 AM
Apr 17 2024 10:53 AM
SolutionTry 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]))))