Home

TextJoin Partial Matches?

%3CLINGO-SUB%20id%3D%22lingo-sub-818660%22%20slang%3D%22en-US%22%3ETextJoin%20Partial%20Matches%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-818660%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20list%20of%20usernames%20that%20have%20access%20to%20one%20or%20more%20location%20codes%20separated%20by%20commas.%20I%20also%20have%20a%20list%20of%20locations%20with%20their%20code%20on%20another%20tab.%20I%20have%20been%20trying%20every%20which%20way%20to%20cross%20reference%20the%20location%20code%20by%20the%20locations%20that%20each%20username%20has%20access%20to%20return%20a%20TextJoin%20of%20which%20usernames%20have%20access%20to%20that%20specific%20location.%20Any%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20550px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128091i3C9B3347C29CE2EB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20365px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128092iB6D39F210F71596B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-818660%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epartial%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETEXTJOIN%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-819513%22%20slang%3D%22en-US%22%3ERe%3A%20TextJoin%20Partial%20Matches%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819513%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F397068%22%20target%3D%22_blank%22%3E%40meklund%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3EPlease%20find%20a%20possible%20solution%20using%20following%20formula%20in%20the%20attached%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%3DTEXTJOIN(%22%2C%22%2CTRUE%2CIF(%20IFERROR(FIND(TRIM(%5B%40Code%5D)%2CUser_access%5BWebsite%20access%5D)%2C)%26gt%3B0%2CUser_access%5BUsername%5D%2C%22%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20note%20that%20the%20data%20has%20been%20formatted%20as%20two%20tables%20-%20User_access%20and%20Location_access.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20it%20works%20for%20you%20!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-819727%22%20slang%3D%22en-US%22%3ERe%3A%20TextJoin%20Partial%20Matches%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819727%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20tweak%20your%20formula%20like%20this%20as%20IFERROR%20and%20%26gt%3B0%20are%20not%20required%20here%20and%20can%20be%20replaced%20with%20ISNUMBER.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DTEXTJOIN(%22%2C%22%2CTRUE%2CIF(ISNUMBER(FIND(TRIM(%5B%40Code%5D)%2CUser_access%5BWebsite%20access%5D))%2CUser_access%5BUsername%5D%2C%22%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-819748%22%20slang%3D%22en-US%22%3ERe%3A%20TextJoin%20Partial%20Matches%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819748%22%20slang%3D%22en-US%22%3EGreat%2C%20that%20works%20too.%20thanks%20for%20refining%20!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-819988%22%20slang%3D%22en-US%22%3ERe%3A%20TextJoin%20Partial%20Matches%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-819988%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20problem!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-820281%22%20slang%3D%22en-US%22%3ERe%3A%20TextJoin%20Partial%20Matches%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-820281%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWorks%20perfect!%20Thanks%20so%20much%20guys.%20Was%20scratching%20my%20head%20on%20that%20one.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-820437%22%20slang%3D%22en-US%22%3ERe%3A%20TextJoin%20Partial%20Matches%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-820437%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F397068%22%20target%3D%22_blank%22%3E%40meklund%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Glad%20we%20could%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E
meklund
New Contributor

Hello, 

 

I have a list of usernames that have access to one or more location codes separated by commas. I also have a list of locations with their code on another tab. I have been trying every which way to cross reference the location code by the locations that each username has access to return a TextJoin of which usernames have access to that specific location. Any suggestions?

 

image.pngimage.png 

 

 

6 Replies
Highlighted

@meklund ,

Please find a possible solution using following formula in the attached file. 

=TEXTJOIN(",",TRUE,IF( IFERROR(FIND(TRIM([@Code]),User_access[Website access]),)>0,User_access[Username],""))

 

Please note that the data has been formatted as two tables - User_access and Location_access. 

 

Hope it works for you !!

 

@Kodipady 

 

You may tweak your formula like this as IFERROR and >0 are not required here and can be replaced with ISNUMBER.

 

=TEXTJOIN(",",TRUE,IF(ISNUMBER(FIND(TRIM([@Code]),User_access[Website access])),User_access[Username],""))

Great, that works too. thanks for refining !!

@Kodipady 

No problem!

@Subodh_Tiwari_sktneer 

@Kodipady 

 

Works perfect! Thanks so much guys. Was scratching my head on that one. 

@meklund 

You're welcome! Glad we could help.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies