need a formula to return header if cell is not blank & concatenate with cell value

[josh_m]

[TABLE="class: grid, width: 449"]
<tbody>[TR]
[TD="align: center"]robert[/TD]
[TD="align: center"]alan[/TD]
[TD="align: center"]josh[/TD]
[TD="align: center"]sara[/TD]
[TD="align: center"]concatenate w/header[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]robert (3) & alan (4) & sara (5)[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]alan (2) & josh (8)[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]alan (1) & sara (6)[/TD]
[/TR]
</tbody>[/TABLE]

let me see if i can explain this clearly.
i am trying to come up with a formula for column E that if the cells in that row's range are not null, return the header value concatenated with the cell value within parentheses, separated by an ampersand.
make sense?

it's easy enough with only 4 columns to use =if(a2<>"",($a$1&" ("&a2&") & ","") & if(b2<>"".....and so on, but my spreadsheet is 21 columns wide, so was hoping an expandable formula would do the trick.

any thoughts on a formula/array that would accomplish this?

 

[Haluk]

I have prepared a file according to your given data and can be reached from the link below.

I am sure you can adopt the file to suit your needs for 21 columns as well.

https://drive.google.com/open?id=0Bw38kVQiZJZEUlBWWjhzT2VfcDA


.

 

[josh_m]

I have prepared a file according to your given data and can be reached from the link below.

I am sure you can adopt the file to suit your needs for 21 columns as well.

https://drive.google.com/open?id=0Bw38kVQiZJZEUlBWWjhzT2VfcDA


.

ok, yeah...i see what you did there...i would need to create a reference columns (which i could hide)...thanks, I believe that will work!
much appreciated

 

[Haluk]

You are welcome