1

I have a dataset which has a seven possible conditions based on data in other columns. I had to split my formulae for those conditions across seven different columns. I would like to combine all seven of those condition columns into a single column.

CONSIDER:

Col A  Col B  Col C  Col D  Col E  Col F  Col G  result
case1                                            case1
                            case5                case5   
       case2                                     case2    
                                   case6         case6
case1                                            case1   
                     case4                       case4    
              case3                              case3 
                                   case6         case6 
                                          case7  case7
                     case4                       case4
              case3                              case3

There will NEVER be a case where there are two values assigned to the same row. I just want to get the ONE data value that occurs in that row to show up in a single column. I could do if(a<>" ",a,if(b<>" ",b,etc... but I'm looking for something simple and elegant. The best I've been able to come up with so far is TRIM(CONCATENATE(A+B+C+D+E+F+G)) but I was wondering if there were a simpler function (like MERGEALLCOLUMNSINTOONE(A:G) that would do this?

Improve this question
2
  • 1
    Note that syntax for concatenating string values would be TRIM(CONCATENATE(A1,B1,C1,D1,E1,F1,G1)), i.e., not using the "+" signs, which would throw an error. Of course, if your case values were numeric, you could just use SUM(A1:G1), for example.
    – chuff
    Commented Apr 14, 2013 at 4:17
  • simply TRIM(A & B .... G)
    – glh
    Commented Apr 15, 2013 at 12:54

2 Answers 2

Reset to default
1

Given the constraint that there is only one value per set of cells then I think the TRIM(CONCATENATE)) option is your best bet. It's kind of elegant in it's own way.

Improve this answer
1
  • 1
    It's anything but elegant. It's clunky (technical term :-)). Concatenate() never adds value. It can always be replaced by just using the & operator. You save eleven letters and two braces. Trim(A1&B1&C1...&G1) does the same thing, but shorter. Elegant can be done, but not with Concatenate().
    – teylyn
    Commented Apr 14, 2013 at 7:54
5

The Trim/Concatenate combo will always return text, even if the actual value is numeric. This formula will return the value with its respective data type retained.

=LOOKUP(2,1/(A2:G2<>""),A2:G2)

Assuming data starts in row 2 and row 1 has labels. Copy down.

Improve this answer
1
  • This may come in handy. Right now, the values are all text, so I'm not terribly worried about it, but as we all know, requirements change. :) good one for the toolbox.
    – dwwilson66
    Commented Apr 15, 2013 at 15:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .