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

Attached Files

My table.xlsx (10.1 KB, 10 views)

Its almost clear as to what you want. I good way of explaining is to include a desired result in your sample sheet. However if I understand right you want to "Concatenate" all of the values in each column into a single cell. The only thing not quite clear is how do you want each value separated. My assumption would be for row 3 with a cross section of 1 would result in 1.25;1.50;1.75;2.00

Is this correct and if so are you against it being a VBA script.

Not exactly. My question is not posed correctly. it is not Merge or Concatenate that i want to do, see attached file.

The function in AA3
=IF(COLUMNS($AA$3:AA$3)>COUNT($C$3:$Y$13);"";SMALL ($C$3:$Y$13;COLUMNS($AA$3:AA$3)))
works well for the defined area ($C$3:$Y$13), all height values are displayed in ascending order, each unique value in a single cell, from AA3 to DY3.
However, I would also like to write a function that is repeatable in the sense that i could just drag it downwards (or copy paste into cell AA20) for the next set of sub sample data (C22:Y39), here called Plot 8, without including the first set of data (Plot 7, ($C$3:$Y$13)).
So I need an altered version of the selection process in the function above. Or a completely new approach?

Attached Files

My_Table3.xlsx (17.6 KB, 6 views)