Excel Formula =Ax&Bx&Cx&Dx... when merged cell appears

Question

I'm working with big table with structure similar to picture below, but more columns(A-AZ) and really more rows. And for some reason I need to get whole row into one string, like you can see as "Expected result", but I'm getting really bad data. Do you know how to get right results? Without VBA, if possible

OK, I don't know how to close this question, but I'm closing it by this. I'm using merged cells, because it's required to use them, and everybody here yells, that merged cells are evil so this question is closed as unsolvable.

Answer 1

To expand on the comments from mattdeak and Brad, the issue is that cells A3:A6 are merged. As a result, the value "Lamp" is actually only in cell A3, and cells A4:A6 are blank. Therefore, J4 sees A4="" and B4="", giving you the indicated result (only the value in C4).

The easiest way, as the commenters noted, is to unmerge the cells and copy all the data into each row. If your cells have to stay merged, I recommend the approach used in this answer. Here's how it works:

1. Pick a space where you can add a second set of Table columns. I'll use CA since you said you have columns A-AZ.
2. In CA3, enter the formula =IF(ISBLANK(A3),CA2,A3).
3. Fill right from CA3 through DZ3. DZ3 should thus refer to AZ3.
4. Fill down CA3:DZ3 for as many rows as you have. At this point. CA3:DZ<last row> is a copy of your table with everything filled in.
5. Update the formula to be =CA3 & CB3 & ... for however many columns you need to merge. Use the values from CA:DZ and you should be OK!