0

I have some values in A1:G1 (a,a,b,c,c,d,d), they are all letters. and in H1, I try to get this result (acd). I want to join only the values that repeat twice ore more. So it should be omitted if there is a single value.

I wrote this formula : =TEXTJOIN(,TRUE,IF(COUNTIF(AY4:BD4,"a")>1,"a",IF(COUNTIF(AY4:BD4,"b")>1,"b",IF(COUNTIF(AY4:BD4,"c")>1,"c",IF(COUNTIF(AY4:BD4,"d")>1,"d","")))))

but it doesn't work. I am not sure if textjoin works this way. I'd be glad if anybody can give me a solution. thanks.

Note: if the values are like this (a,a,a,a,a,b,c), then the result should be (a).

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Max
  • 932
  • 1
  • 10
  • 20
  • 1
    what about `=IF(COUNTIF(AY4:BD4,"a")>1,"a","")&IF(COUNTIF(AY4:BD4,"b")>1,"b","")&IF(COUNTIF(AY4:BD4,"c")>1,"c","")&IF(COUNTIF(AY4:BD4,"d")>1,"d","")` ? Maybe TEXTJOIN is possible too, like this : `=TEXTJOIN(" ",TRUE,IF(COUNTIF(AY4:BD4,"a")>1,"a",IF(COUNTIF(AY4:BD4,"b")>1,"b",IF(COUNTIF(AY4:BD4,"c")>1,"c",IF(COUNTIF(AY4:BD4,"d")>1,"d","")))))` – fictimaph Apr 12 '18 at 12:31
  • Thanks for your suggestion. – Max Apr 12 '18 at 13:04

2 Answers2

2

Use this as an array formula:

=TEXTJOIN(",",TRUE,IF((COUNTIFS(A1:G1,A1:G1)>1)*(COLUMN(A1:G1)-COLUMN(A1)+1=MATCH(A1:G1,A1:G1,0)),A1:G1,""))

Being an array formula is needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
0

In A2 enter:

=IF(COUNTIF($A$1:A1,A1)=2,A1,"")

and copy across. Then in H1 enter:

=TEXTJOIN("",TRUE,A2:G2)

enter image description here

and in your second example:

enter image description here

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Thanks for your solution, I'll go without helper cells since there is too much data. – Max Apr 12 '18 at 13:03