Selecting values from different rows in a table and merge them in order in a singlerow

[FromF]

Hi,

I want to use a formula, or set of formulas, for selecting values from a data table were the number of values differ between rows (see attachment). I do not want to cut and paste or use any commands as I need to make this a executable function.

For instance, 9 rows and 21 cols (as in attachment), in the first row i have 21 values, in the 2nd only 4 and in the 3rd I have 5 etc. I want to "merge" these values in order in a single row, i.e. from B2 to V2 (in this case) and then continue with B3 to E3, and B4 to F4 etc, all in a single new row.

So in the table I have a total of 103 values in 9 rows, i want to sort them in a single row, i.e. from e.g. B15 to CZ15 (I think it will be CZ).


Thanks!
Fredrik

 

[Hui]

Frederick

Firstly, Welcome to the Chandoo.org Forums

When you say Merge do you mean Add or Concatenate?

If it's Add
W2: =Sum(B2:W2)

If it's Merge
You may want to look at using a User Defined Function (UDF)
I'd suggest using Concat() or ConcatIf()
Read this post especially the comments
http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/