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

@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
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies