1

I have a 2D matrix of binary values. I'd like to find all the 1's in a row, and return their respective header in the form of an array or comma delimited string in one cell.

I could do it in VBA, but for my knowledge I wanted to know if there was a handy way to do it in Excel.

EEM
  • 6,601
  • 2
  • 18
  • 33
George Terziev
  • 129
  • 1
  • 3
  • 14
  • 1
    The short answer is: 'there is a way to do this, and whether or not it is handy, it is pretty ugly". Basically, to get this to work one needs multiple cells in the answer space and a well-defined data area. See here: http://stackoverflow.com/a/31325935/5090027 and here: http://stackoverflow.com/q/33112330/5090027 – Grade 'Eh' Bacon Nov 09 '15 at 14:40
  • If you add some details on how exactly you mean to do this, I could summarize the above into a workable solution. The key is - would you be okay with having either: (1) only the top, say, 5 results show? OR (2) having an answer space which effectively takes up as many rows as you have in your data block? (Note that you specifically ask if you can get it in a single string or an array result - this is only possible if you can hardcode the # of value responses you expect to get, and even then you need to duplicate the same formula multiple times in a single cell, or use a large 'helper' column). – Grade 'Eh' Bacon Nov 09 '15 at 14:42
  • [Concatenate top row cells if column below has 1](http://stackoverflow.com/questions/28679758/concatenate-top-row-cells-if-column-below-has-1/28680713#28680713) has VBA and non-VBA responses. –  Nov 09 '15 at 14:50

1 Answers1

0

Assuming your matrix is located at B6:F12 (adjust ranges in formula and arguments as required)

Enter this FormulaArray at G7 and copy till last record (Enter the FormulaArray pressing [Ctrl] + [Shift] + [Enter] simultaneously, you shall see { } around the formula if entered correctly)

=TRIM(CONCATENATE(
IF($B7:$B12,$B$6,""),CHAR(32),
IF($C7:$C12,$C$6,""),CHAR(32),
IF($D7:$D12,$D$6,""),CHAR(32),
IF($E7:$E12,$E$6,""),CHAR(32),
IF($F7:$F12,$F$6,"")))

This formula concatenates the result of 5 fields separated by a blank space. You'll have to add one more argument per additional field.

enter image description here

This standard formula will also do the trick:

=TRIM(SUBSTITUTE(CONCATENATE(
IF($B7,", "&$B$6,""),
IF($C7,", "&$C$6,""),
IF($D7,", "&$D$6,""),
IF($E7,", "&$E$6,""),
IF($F7,", "&$F$6,"")),",","",1))

Both formulas will certainly become longer as the number of fields increases, however as the formula is quite repetitive an excel formula can be used to build it.

EEM
  • 6,601
  • 2
  • 18
  • 33