Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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 Apr 14 '13 at 4:17
    
simply TRIM(A & B .... G) – glh Apr 15 '13 at 12:54
up vote 1 down vote accepted

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.

share|improve this answer
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 Apr 14 '13 at 7:54

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.

share|improve this answer
    
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 Apr 15 '13 at 15:04

You must log in to answer this question.

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