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

FromF

New Member
Joined
Sep 5, 2014
Messages
3
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).

[TABLE="class: grid, width: 1492"]
<tbody>[TR]
[TD]Height of cross section[/TD]
[TD] B[/TD]
[TD]C [/TD]
[TD] D[/TD]
[TD] E[/TD]
[TD] F[/TD]
[TD] ...[/TD]
[TD] ..[/TD]
[TD] .[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0,05[/TD]
[TD="align: right"]0,09[/TD]
[TD="align: right"]0,14[/TD]
[TD="align: right"]0,18[/TD]
[TD="align: right"]0,23[/TD]
[TD="align: right"]0,27[/TD]
[TD="align: right"]0,32[/TD]
[TD="align: right"]0,36[/TD]
[TD="align: right"]0,41[/TD]
[TD="align: right"]0,45[/TD]
[TD="align: right"]0,50[/TD]
[TD="align: right"]0,55[/TD]
[TD="align: right"]0,59[/TD]
[TD="align: right"]0,64[/TD]
[TD="align: right"]0,68[/TD]
[TD="align: right"]0,73[/TD]
[TD="align: right"]0,77[/TD]
[TD="align: right"]0,82[/TD]
[TD="align: right"]0,86[/TD]
[TD="align: right"]0,91[/TD]
[TD="align: right"]1,00[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1,25[/TD]
[TD="align: right"]1,50[/TD]
[TD="align: right"]1,75[/TD]
[TD="align: right"]2,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2,20[/TD]
[TD="align: right"]2,40[/TD]
[TD="align: right"]2,60[/TD]
[TD="align: right"]2,80[/TD]
[TD="align: right"]3,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]3,17[/TD]
[TD="align: right"]3,33[/TD]
[TD="align: right"]3,50[/TD]
[TD="align: right"]3,67[/TD]
[TD="align: right"]3,83[/TD]
[TD="align: right"]4,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]4,13[/TD]
[TD="align: right"]4,25[/TD]
[TD="align: right"]4,38[/TD]
[TD="align: right"]4,50[/TD]
[TD="align: right"]4,63[/TD]
[TD="align: right"]4,75[/TD]
[TD="align: right"]4,88[/TD]
[TD="align: right"]5,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]5,11[/TD]
[TD="align: right"]5,22[/TD]
[TD="align: right"]5,33[/TD]
[TD="align: right"]5,44[/TD]
[TD="align: right"]5,56[/TD]
[TD="align: right"]5,67[/TD]
[TD="align: right"]5,78[/TD]
[TD="align: right"]5,89[/TD]
[TD="align: right"]6,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]6,07[/TD]
[TD="align: right"]6,14[/TD]
[TD="align: right"]6,21[/TD]
[TD="align: right"]6,29[/TD]
[TD="align: right"]6,36[/TD]
[TD="align: right"]6,43[/TD]
[TD="align: right"]6,50[/TD]
[TD="align: right"]6,57[/TD]
[TD="align: right"]6,64[/TD]
[TD="align: right"]6,71[/TD]
[TD="align: right"]6,79[/TD]
[TD="align: right"]6,86[/TD]
[TD="align: right"]6,93[/TD]
[TD="align: right"]7,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]7,10[/TD]
[TD="align: right"]7,20[/TD]
[TD="align: right"]7,30[/TD]
[TD="align: right"]7,40[/TD]
[TD="align: right"]7,50[/TD]
[TD="align: right"]7,60[/TD]
[TD="align: right"]7,70[/TD]
[TD="align: right"]7,80[/TD]
[TD="align: right"]7,90[/TD]
[TD="align: right"]8,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]8,07[/TD]
[TD="align: right"]8,14[/TD]
[TD="align: right"]8,21[/TD]
[TD="align: right"]8,29[/TD]
[TD="align: right"]8,36[/TD]
[TD="align: right"]8,43[/TD]
[TD="align: right"]8,50[/TD]
[TD="align: right"]8,57[/TD]
[TD="align: right"]8,64[/TD]
[TD="align: right"]8,71[/TD]
[TD="align: right"]8,79[/TD]
[TD="align: right"]8,86[/TD]
[TD="align: right"]8,93[/TD]
[TD="align: right"]9,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]9,16[/TD]
[TD="align: right"]9,33[/TD]
[TD="align: right"]9,49[/TD]
[TD="align: right"]9,65[/TD]
[TD="align: right"]9,82[/TD]
[TD="align: right"]9,98[/TD]
[TD="align: right"]10,15[/TD]
[TD="align: right"]10,31[/TD]
[TD="align: right"]10,47[/TD]
[TD="align: right"]10,64[/TD]
[TD="align: right"]10,80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10,8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks!
Fredrik
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
English is not my first language so i might not have explained it very well. I have added a smaller sample of my data with the desired result and the result from the original StringConcat.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 168"]Height[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0,05[/TD]
[TD="align: right"]0,09[/TD]
[TD="align: right"]0,14[/TD]
[TD="align: right"]0,18[/TD]
[TD="align: right"]0,23[/TD]
[TD="align: right"]0,27[/TD]
[TD="align: right"]0,32[/TD]
[TD="align: right"]0,36[/TD]
[TD="align: right"]0,41[/TD]
[TD="align: right"]0,45[/TD]
[TD="align: right"]0,50[/TD]
[TD="align: right"]0,55[/TD]
[TD="align: right"]0,59[/TD]
[TD="align: right"]0,64[/TD]
[TD="align: right"]0,68[/TD]
[TD="align: right"]0,73[/TD]
[TD="align: right"]0,77[/TD]
[TD="align: right"]0,82[/TD]
[TD="align: right"]0,86[/TD]
[TD="align: right"]0,91[/TD]
[TD="align: right"]1,00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1,25[/TD]
[TD="align: right"]1,50[/TD]
[TD="align: right"]1,75[/TD]
[TD="align: right"]2,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2,20[/TD]
[TD="align: right"]2,40[/TD]
[TD="align: right"]2,60[/TD]
[TD="align: right"]2,80[/TD]
[TD="align: right"]3,00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So what i want to do is to put the values from all rows in one single row like this:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 168"][/TD]
[TD="width: 128, colspan: 2"](from row 2)[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 128, colspan: 2"](From row 3)[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 128, colspan: 2"](From row 4)[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]Desired result ->[/TD]
[TD="align: right"]0,05[/TD]
[TD="align: right"]0,09[/TD]
[TD="align: right"]0,14[/TD]
[TD="align: right"]0,18[/TD]
[TD="align: right"]0,23[/TD]
[TD="align: right"]0,27[/TD]
[TD="align: right"]0,32[/TD]
[TD="align: right"]0,36[/TD]
[TD="align: right"]0,41[/TD]
[TD="align: right"]0,45[/TD]
[TD="align: right"]0,50[/TD]
[TD="align: right"]0,55[/TD]
[TD="align: right"]0,59[/TD]
[TD="align: right"]0,64[/TD]
[TD="align: right"]0,68[/TD]
[TD="align: right"]0,73[/TD]
[TD="align: right"]0,77[/TD]
[TD="align: right"]0,82[/TD]
[TD="align: right"]0,86[/TD]
[TD="align: right"]0,91[/TD]
[TD="align: right"]1,00[/TD]
[TD="align: right"]1,25[/TD]
[TD="align: right"]1,50[/TD]
[TD="align: right"]1,75[/TD]
[TD="align: right"]2,00[/TD]
[TD="align: right"]2,20[/TD]
[TD="align: right"]2,40[/TD]
[TD="align: right"]2,60[/TD]
[TD="align: right"]2,80[/TD]
[TD="align: right"]3,00[/TD]
[/TR]
</tbody>[/TABLE]


with one cell for each value.

When I use the "=StringConcat("|";B2:W4)" all values are merged in one cell.

[TABLE="class: grid, width: 232"]
<tbody>[TR]
[TD="width: 168"]Using UDF StringConcat ->[/TD]
[TD="width: 64"]0,045455|0,090909|0,136364|0,181818|0,227273|0,272727|0,318182|0,363636|0,409091|0,454545|0,5|0,545455|0,590909|0,636364|0,681818|0,727273|0,772727|0,818182|0,863636|0,909091|1|1,25|1,5|1,75|2|2,2|2,4|2,6|2,8|3[/TD]
[/TR]
</tbody>[/TABLE]

I could use copy and paste special (values) to put the information from StringConcat in a new cell and then use the command text to columns to separate the values in to separate cells for each value, however, I want to use a function(s) instead of commands as I need to make this an executable function.

Cheers!
Fredrik
 
Upvote 0
Sorry I misunderstood. Formula in B6 copied across:


Excel 2010
ABCDEFGHIJKLMNOP
1Height
200,050,090,140,180,230,27
311,251,501,752,00
422,202,402,602,803,00
5
6Result0,050,090,140,180,230,271,251,501,752,002,202,402,602,803,00
Sheet1
Cell Formulas
RangeFormula
B6{=IFERROR(INDIRECT(TEXT(SMALL(IF($B$2:$G$4<>"",ROW($B$2:$G$4)*10^4+COLUMN($B$2:$G$4)),COLUMNS($B6:B6)),"R0000C0000"),0),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,483
Messages
6,172,519
Members
452,463
Latest member
Debz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top