Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.