Split column values to new record without repeating initial column's data

[ssphv]

Hello,

I have a data with 20 columns. Last column have comma separated values.  I would like to split values in last column to new row. 

Column1	Column 2	Column 3	…………				Column 20
123	456	789					258, 369,125,869,555,769
111	2222	536					369,452,127,699,56423, 258631, 6666,7777
1	2	3					5,6,7,8, ……………….50

Below is the output what i'm looking for. ' Text to columns' can be used to split into rows but it splits with repeating values from columns 1 to column 20. If the values in column20 are greater than 20, they must be split into new record and into different columns in new record.For example, from the above table 3rd row last column(contains integers from 5 to 50)  must be split into 3 rows, 6 to 25, 26 to 45 and 46 to 50 

Column1	Column 2	Column 3	………	….					Column 20
123	456	789							258
369	125	869	555	769
111	2222	536							396
452	127	699	56423	258631	6666	7777
1	2	3
6	7	8							25
26	27	28							45
46	47	48	49	50

Any suggestions or guidance will be helpful. Thank you in advance!

[CharlieS]

There's probably a shorter way to do this, but I believe this achieves the desired output. There's a key parts to this that make this possible:

- Change the RecordID to a FixedDeicmal type so that added rows could be re-added into the right order between the original records

- The CEIL( function using the sequence number/20 will specify which group of 20 records it should belong to.

The only problem will be if you have a Field20 that has more than 1980 values. This would add more than 0.99 to the RecordID. If this is the case, then extend the decimal place of the RecordID and the multiplier used in the CEIL( function. 

[ssphv]

Hi Charlie,

Thank you for the solution, this solution is close by but this is exactly where i'm stuck, the value in field20 to being moved to next row. See the one's that I marked in bold. 258 must be in recordID 1.0 in Field20, 369 must be in recordID 2.0 in Field20 and sample applies to other.

RecordID	Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8	Field9	Field10	Field11	Field12	Field13	Field14	Field15	Field16	Field17	Field18	Field19	Field20
1.0	123	456	789
1.1	258	369	125	869	555	769
2.0	111	2222	536
2.1	369	452	127	699	56423	258631	6666	7777
3.0	1	2	3
3.1	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24
3.2	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44
3.3	45	46	47	48	49	50

[CharlieS · Accepted solution]

Oh ok. I made a few changes to make that happen. Let me know if this helps.

[ssphv]

Hi Charlie,

That works perfectly. Thank you!