SOLVED

Texjoin to remove duplicates

%3CLINGO-SUB%20id%3D%22lingo-sub-1352882%22%20slang%3D%22en-US%22%3ETexjoin%20to%20remove%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1352882%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20file%20I'm%20having%20issues%20with.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20column%20with%20list%20of%20units%20that%20includes%20duplicates.%20Next%20to%20that%20column%20is%20a%20list%20of%20Ages%20with%20more%20duplicates.%20For%20example%2C%20for%20said%20unit%201203%2C%20I%20have%20in%20column%20A%2017%20rows%20with%20that%20same%20unit%20number.%20In%20column%20B%2C%20I%20have%2016%20rows%20showing%20age%2039%2C%20and%20one%20row%20showing%20age%2034.%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20column%20F%2C%20I%20have%20all%20the%20units%20where%20I%20removed%20duplicates.%20Next%20to%20it%20in%20column%20H%20actually%2C%20I%20am%20trying%20to%20use%20the%20textjoin%20to%20see%20the%20ages%20without%20any%20duplicates.%20In%20example%20above%2C%20I%20need%20to%20see%2034%2C39.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20formula%20I%20am%20using%20in%20H2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DTEXTJOIN(%22%2C%22%2CTRUE%2CIF((%24A%242%3A%24A%24683%3D%24F2)*(MATCH(%24B%242%3A%24B%24683%2C%24B%242%3A%24B%24683%2C0)%3D(ROW(%24B%242%3A%24B%24683)-ROW(%24B%241)))%2C%24B%242%3A%24B%24683%2C%22%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help.%20File%20is%20attached.%20Thanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1352882%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1352998%22%20slang%3D%22en-US%22%3ERe%3A%20Texjoin%20to%20remove%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1352998%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F644335%22%20target%3D%22_blank%22%3E%40ztimothy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20365%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DTEXTJOIN(%22%2C%22%2CTRUE%2CSORT(UNIQUE(FILTER(B%3AB%2CA%3AA%3DF2))%2C1%2C1))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1353048%22%20slang%3D%22en-US%22%3ERe%3A%20Texjoin%20to%20remove%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1353048%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20very%20much%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1353179%22%20slang%3D%22en-US%22%3ERe%3A%20Texjoin%20to%20remove%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1353179%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F644335%22%20target%3D%22_blank%22%3E%40ztimothy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome.%20Have%20a%20great%20weekend!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello, 

 

Attached file I'm having issues with.

 

I have a column with list of units that includes duplicates. Next to that column is a list of Ages with more duplicates. For example, for said unit 1203, I have in column A 17 rows with that same unit number. In column B, I have 16 rows showing age 39, and one row showing age 34. 

In column F, I have all the units where I removed duplicates. Next to it in column H actually, I am trying to use the textjoin to see the ages without any duplicates. In example above, I need to see 34,39.

 

Here is the formula I am using in H2.

 

=TEXTJOIN(",",TRUE,IF(($A$2:$A$683=$F2)*(MATCH($B$2:$B$683,$B$2:$B$683,0)=(ROW($B$2:$B$683)-ROW($B$1))),$B$2:$B$683,""))

 

Please help. File is attached. Thanks in advance.

 

@Haytham Amairah

@Sergei Baklan

3 Replies
best response confirmed by ztimothy (New Contributor)
Solution

@ztimothy 

If you have 365:

 

=TEXTJOIN(",",TRUE,SORT(UNIQUE(FILTER(B:B,A:A=F2)),1,1))

Thank you so very much @Patrick2788 

@ztimothy 

You're welcome. Have a great weekend!