1

Basically, I have an excel sheet similar to the below. I want to use the YES cells to create an equation for the source column which indicates the source of the yeses.

Header 1 Header 2 Header 3 Source
YES NO NO Header 1
NO YES NO Header 2
NO YES YES Header 2 and Header 3
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
kksweene
  • 9
  • 1
  • 1
    What version of Excel do you have? – BigBen Jul 22 '22 at 18:54
  • 1
    This should work if you are using MS365 `=TEXTJOIN(" and ",,FILTER($A$1:$C$1,A2:C2="YES"))` – Mayukh Bhattacharya Jul 22 '22 at 18:58
  • Perhaps if you are not using MS365, and there are only three columns to create an equation for source column then , `=INDEX($A$1:$C$1,AGGREGATE(15,6,(COLUMN($A2:$C2)-COLUMN($A2)+1)/($A2:$C2="YES"),COLUMN(A1)))&IFERROR(" and "&INDEX($A$1:$C$1,AGGREGATE(15,6,(COLUMN($A2:$C2)-COLUMN($A2)+1)/($A2:$C2="YES"),COLUMN(B1))),"")&IFERROR(" and "&INDEX($A$1:$C$1,AGGREGATE(15,6,(COLUMN($A2:$C2)-COLUMN($A2)+1)/($A2:$C2="YES"),COLUMN(C1))),"")` but its a bit ugly formula. – Mayukh Bhattacharya Jul 22 '22 at 19:04
  • @BigBen Standard - 2019 – kksweene Jul 22 '22 at 19:25
  • @MayukhBhattacharya Any ideas for a simpler formula? – kksweene Jul 22 '22 at 19:25
  • This works with Excel 2019, `=TEXTJOIN(" and ",,IFERROR(INDEX($A$1:$C$1,AGGREGATE(15,6,(COLUMN($A2:$C2)-COLUMN($A2)+1)/($A2:$C2="YES"),{1,2,3})),""))` you need to keyed with `CTRL+SHIFT+ENTER` . Note assuming you have three columns if its more than that, then just change those {1,2,3} to `COLUMN(A1:Z1)` – Mayukh Bhattacharya Jul 22 '22 at 19:36

2 Answers2

2

For 2019, use some alternative approaches.

FORMULA_SOLUTION

• Formula used in cell D2

=TEXTJOIN(" and ",,REPT($A$1:$C$1,A2:C2="YES"))

FORMULA_SOLUTION

• Formula used in cell D2

=SUBSTITUTE(TEXTJOIN(", ",,REPT($A$1:$C$1,A2:C2="YES")),", ",
" and ",IF(COUNTIF(A2:C2,"YES")-1=0,"1",COUNTIF(A2:C2,"YES")-1))

enter image description here

• Formula used in cell F2

=TEXTJOIN(" and ",,IFERROR(INDEX($A$1:$C$1,AGGREGATE(15,6,(
COLUMN($A2:$C2)-COLUMN($A2)+1)/($A2:$C2="YES"),COLUMN(A1:Z1))),""))

Need to press CTRL + SHIFT + ENTER

Edit, replacing the last comma with and, which will be the right thing to use,

FORMULA_SOLUTION

• Formula used in cell F2

=SUBSTITUTE(TEXTJOIN(", ",,IFERROR(
INDEX($A$1:$C$1,AGGREGATE(15,6,(
COLUMN($A2:$C2)-COLUMN($A2)+1)/($A2:$C2="YES"),COLUMN(A1:Z1))),"")),", "," and ",
IF(COUNTIF(A2:C2,"YES")-1=0,"1",COUNTIF(
A2:C2,"YES")-1))
Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32
1

For Excel 2019, try:

enter image description here

Formula in F2:

=TEXTJOIN(" and ",,IF(A2:C2="YES",A$1:C$1,""))

Confirm through CSE and drag down.

Or;

=SUBSTITUTE(TEXTJOIN(", ",,IF(A2:C2="YES",A$1:C$1,"")),", "," and ",MAX(1,COUNTIF(A2:C2,"YES")-1))

enter image description here

JvdV
  • 70,606
  • 8
  • 39
  • 70