Excel : Columns by row value [duplicate]

问题:

This question already has an answer here:

• Concatenate column headers if value in rows below is non-blank 1 answer
• Concatenate cell data into another data if values matches 3 answers
• Concatenate top row cells if column below has 1 2 answers

Is there a method without using macro or script, to get this result?

Bus Line    L1  L2  L3  L4  Result
Stops                   
1               X       X   L2,L4
2                       X   L4
3           X       X       L1,L3

I need to concatenate COLUMN'S NAME for each "X" on rows.

回答1:

In F2 enter the array formula:

=TEXTJOIN(",",TRUE,IF(B2:E2="X",$B$1:$E$1,""))

and copy down:

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

回答2:

Use an array version of TEXTJOIN()

=TEXTJOIN(",",TRUE,IF(B2:E2="x",$B$1:$E1,""))

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

You can also use helper columns. In H2 put:

=IF(B2="X","," &B$1,"")

Drag over 4 columns and down the length of the data.

Then in F2 put:

=MID(H2&I2&J2&K2,2,99)

The other option is a long formula that combines the two steps above:

=MID(IF(B2="X","," &B$1,"")&IF(C2="X","," &C$1,"")&IF(D2="X","," &D$1,"")&IF(E2="X","," &E$1,""),2,99)