Excel : Columns by row value

Question

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.

Yeah. I was trying to use MATCH, INDEX or LOOKUP. For example =MATCH("X";B1:E3;0) dosn't find anything. Orizzontal lookup search the first row and then get the row you want in the same column but I want the inverse. — RetroMime, Mar 12 '18 at 15:45

Scott Craner · Answer 1 · 2018-03-12T16:11:57.143

0

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)

edited Mar 12 '18 at 16:11

answered Mar 12 '18 at 15:38

Scott Craner

148,073
10
49
81

I don't have textjoin. Its seems avaliable only in Excel 365 – RetroMime Mar 12 '18 at 15:58
Then look at the duplicate, because you will need vba. – Scott Craner Mar 12 '18 at 15:59
That return to my original question: without using macro or script. So the answer is "NO" – RetroMime Mar 12 '18 at 16:02
I guess you could use a bunch of helper cells. – Scott Craner Mar 12 '18 at 16:03
@RetroMime to avoid vba, the options are above. Otherwise vba is your only choice. – Scott Craner Mar 12 '18 at 16:14
To resolve, Im using Google Sheet that has textjoin function. The Syntax is similar. Its only need to wrap the "IF" condition in ArrayFormula. – RetroMime Mar 12 '18 at 16:32

score 0 · Accepted Answer · answered Mar 12 '18 at 15:41

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.

Excel : Columns by row value

2 Answers2

Linked

Related