TEXTJOIN function

[Ufoo]

Hello excel experts,

I thank Mike Girvin for teaching me how to extract unique items and list them in a single column using TEXTJOIN function ("

"). I have used the function to make the attached list of salesperson in a number of countries. The problem is that if the salesperson is working in more than one country (which is quite normal in real life), the formula returns his name in one country only. Please let us figure out and discuss how to address this challenge. I will also appreciate more ideas from you on how to list items in 1 column/ use TEXTJOIN function.

 

[John Jairo V]

Hi @Ufoo

You could use this formulae (no CSE):

=TEXTJOIN(", ",,IF(MMULT(N(($A$2:$A$40=D2)*MATCH($A$2:$A$40&"-"&$B$2:$B$40,$A$2:$A$40&"-"&$B$2:$B$40,)=ROW($A$2:$A$40)-ROW($A$1)),1),$B$2:$B$40,""))

Blessings!

 

[Ufoo]

Hi @Ufoo

You could use this formulae (no CSE):

=TEXTJOIN(", ",,IF(MMULT(N(($A$2:$A$40=D2)*MATCH($A$2:$A$40&"-"&$B$2:$B$40,$A$2:$A$40&"-"&$B$2:$B$40,)=ROW($A$2:$A$40)-ROW($A$1)),1),$B$2:$B$40,""))

Blessings!

Thanks a lot @john Jairo. It has worked with CSE. I am now figuring out how to have the extracted names arranged alphabetically

 

[Ufoo]

I have made another formula for extracting the names in an alphabetical order. Again thanks to Mike Girvin (Excel magic trick 1313 on youtube). My formula has calculated the first 23 countries (refer to the table attached above). The computer failed to extract names for USA with the message excel an out of resources while attempting to calculate one or more formulas.... I think this is due to the fact that the formula is too big. I will appreciate inputs: =INDEX(INDEX($B$2:$B$49,N(IF(1,SMALL(IF(A$2:A$49=D2,IF(MATCH(A$2:A$49&B$2:$B$49,A$2:A$49&$B$2:$B$49,0)=ROW($A$2:$A$49)-MIN(ROW($A$2:$A$49))+1,ROW($A$2:$A$49)-MIN(ROW($A$2:$A$49))+1)),ROW(INDIRECT("a1:a"&COUNTIF($A$2:$A$49,D2))))))),N(IF(1,MATCH(ROW(INDIRECT("a1:a"&COUNTIF($A$2:$A$49,D2))),MMULT(--(INDEX($B$2:$B$49,N(IF(1,SMALL(IF(A$2:A$49=D2,IF(MATCH(A$2:A$49&B$2:$B$49,A$2:A$49&$B$2:$B$49,0)=ROW($A$2:$A$49)-MIN(ROW($A$2:$A$49))+1,ROW($A$2:$A$49)-MIN(ROW($A$2:$A$49))+1)),ROW(INDIRECT("a1:a"&COUNTIF($A$2:$A$49,D2)))))))>=TRANSPOSE(INDEX($B$2:$B$49,N(IF(1,SMALL(IF(A$2:A$49=D2,IF(MATCH(A$2:A$49&B$2:$B$49,A$2:A$49&$B$2:$B$49,0)=ROW($A$2:$A$49)-MIN(ROW($A$2:$A$49))+1,ROW($A$2:$A$49)-MIN(ROW($A$2:$A$49))+1)),ROW(INDIRECT("a1:a"&COUNTIF($A$2:$A$49,D2))))))))),ROW(INDIRECT("a1:a"&COUNTIF($A$2:$A$49,D2)))^0),0))))

 

[NARAYANK991]

Hi ,

Most people who use Excel do so for improving their productivity at work.

Those who are not in the above category , develop formulas such as the one you posted.

Most forums are devoted to helping people in the first category.

Since I am also in the first category , the only input I can give is to stop using or developing such formulas , unless your interest is to see how far you can stretch your mind and of course Excel itself.

If you wish to improve productivity at work , start using other features of Excel such as Pivot Tables , helper columns.

Narayan

 

[Ufoo]

Hi ,

Most people who use Excel do so for improving their productivity at work.

Those who are not in the above category , develop formulas such as the one you posted.

Most forums are devoted to helping people in the first category.

Since I am also in the first category , the only input I can give is to stop using or developing such formulas , unless your interest is to see how far you can stretch your mind and of course Excel itself.

If you wish to improve productivity at work , start using other features of Excel such as Pivot Tables , helper columns.

Narayan

I think I am in the above category too @NARAYANK991. The formula results from my quest of finding a formula for extracting a unique list of items into 1 cell. I was just wondering if excel can go to that extent.

 

[Lori]

@Ufoo: did you see Mike Girvin's follow up in magic trick 1314:

I believe the second formula in that video can be adapted to your needs (suggested by a certain lori_m by coincidence perhaps ) Also see the following video in magic trick 1315 to see how the process can be achieved very simply in power query.

 

[Ufoo]

@Ufoo: did you see Mike Girvin's follow up in magic trick 1314:

I believe the second formula in that video can be adapted to your needs (suggested by a certain lori_m by coincidence perhaps ) Also see the following video in magic trick 1315 to see how the process can be achieved very simply in power query.

Thanks @Lori. I have watched the follow on video. Hence the formula below. And I think you are the same Lori behind the formula below. Mike Girvin thanked you and I thank you as well. Your technique is far more efficient and simpler: =TEXTJOIN(", ",,IFNA(INDEX($B$2:$B$67,N(IF(1,MATCH(ROW($A$2:$A$67)-MIN(ROW($A$2:$A$67))+1,MMULT(($A$2:$A$67=D2)*($B$2:$B$67>=TRANSPOSE($B$2:$B$67)),1^ROW($A$2:$A$67)),0)))),""))

 

[bines53]

I remembered this thread,

http://forum.chandoo.org/threads/a-new-method-to-distinct-values.21999/

David

 

[Chihiro]

This is easy using PowerQuery.

1. Load table to PowerQuery.
2. Sort on Country then Salesperson
3. Goup by Country, Choose "All Rows". Named grouped column "SalesP"
4. Add custom column with following formula
=Text.Combine(List.Distinct([SalesP][Salesperson]),", ")
5. Remove "SalesP" column.

Complete M formula.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Salesperson", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Country", Order.Ascending}, {"Salesperson", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Country"}, {{"SalesP", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Salesperson", each Text.Combine(List.Distinct([SalesP][Salesperson]),", ")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"SalesP"})
in
    #"Removed Columns"

See attached sample.

 

[Ufoo]

@Ufoo: did you see Mike Girvin's follow up in magic trick 1314.

I believe the second formula in that video can be adapted to your needs (suggested by a certain lori_m by coincidence perhaps ) Also see the following video in magic trick 1315 to see how the process can be achieved very simply in power query.

Hi @Lori and other experts, in the attached excel sheet I have attempted to use COUNTIF instead of MMULT. It has worked in Sheet2, but hasn't worked in Sheet 1 (Excel Magic trick 1314). Any ideas why this is the case? Thanks