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

[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).

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

<tbody>
</tbody>

Thanks!
Fredrik

 

[Andrew Poulsom]

Maybe this will help you:

String Concatenation

Example:

=StringConcat(",",B3:V3)

 

[FromF]

Maybe this will help you:

String Concatenation

Example:

=StringConcat(",",B3:V3)

Hi Andrew,

Thanks alot! Do you know how I can skip the cells that contains no values? and also if the original cell values can be represented in separate cells, following each other in a single row?

 

[Andrew Poulsom]

Sorry, I don't understand. Can you post a smaller sample of your data and the expected results?

 

[FromF]

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.

Height
0	0,05	0,09	0,14	0,18	0,23	0,27	0,32	0,36	0,41	0,45	0,50	0,55	0,59	0,64	0,68	0,73	0,77	0,82	0,86	0,91	1,00
1	1,25	1,50	1,75	2,00
2	2,20	2,40	2,60	2,80	3,00

<tbody>
</tbody>

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

	(from row 2)																				(From row 3)			(From row 4)
Desired result ->	0,05	0,09	0,14	0,18	0,23	0,27	0,32	0,36	0,41	0,45	0,50	0,55	0,59	0,64	0,68	0,73	0,77	0,82	0,86	0,91	1,00	1,25	1,50	1,75	2,00	2,20	2,40	2,60	2,80	3,00

<tbody>
</tbody>

with one cell for each value.

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

Using UDF StringConcat ->

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

<tbody>
</tbody>

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

 

[Andrew Poulsom]

Sorry I misunderstood. Formula in B6 copied across:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Height
2	0	0,05	0,09	0,14	0,18	0,23	0,27
3	1	1,25	1,50	1,75	2,00
4	2	2,20	2,40	2,60	2,80	3,00
5
6	Result	0,05	0,09	0,14	0,18	0,23	0,27	1,25	1,50	1,75	2,00	2,20	2,40	2,60	2,80	3,00
Sheet1

Cell Formulas
Range	Formula
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.