Concat Help..Increment Autofill by a fixed number

[paulselman]

Hi, I hope someone can help me...

I am using Excel to prepare some data for an automated service to do mass deletions from a system

I have a list with circa 150000 references on it. I have to create a single cell with a maximum of 25000 characters in it and each individual reference has to be separated by a semicolon

The data looks something like this

18045;

18152;

18154;

18157;

18161;

I need to combine a maximum of 4000 cells in a row of reference numbers with semicolon as above but in order for the system that will take in the data to work the cell with the result of the concat must have no more than 25000 characters.

I am looking for a way to prepare the data based on that criteria for the entire column that contains 150000 rows so that I end up with a series of rows that I can just paste onto the next system. I have used CONCAT but dragging this down does not increment as I need it to

Thanks in advance

 

[Kerryx]

Would something like this help

Book2
	A	B	C
1	18045;	18045;	6
2	18152;	18045;18152;	12
3	18154;	18045;18152;18154;	18
4	18157;	18045;18152;18154;18157;	24
5	18161;	18045;18152;18154;18157;18161;	30
Sheet1

Cell Formulas
Range	Formula
B1	B1	=A1
C1:C5	C1	=LEN(B1)
B2:B5	B2	=B1&A2

 

[Fluff]

Hi & welcome to MrExcel.
If your data is always 6 characters long how about

+Fluff v1.xlsm
	A	B	C
1
2	49635;		49635;53012;37754;29271;50713;53809;43520;53169;43361;50778;
3	53012;		55880;36306;42612;34869;48040;51821;53189;42386;43575;53499;
4	37754;		53299;39376;42649;45421;41521;40123;40182;45229;44270;51388;
5	29271;		51846;54072;52813;48767;45989;43860;51052;45479;40880;47094;
6	50713;		50068;42317;42399;35831;56421;52321;46109;50848;52646;48636;
7	53809;		52545;43621;59612;
8	43520;
9	53169;
10	43361;
11	50778;
12	55880;
13	36306;
14	42612;
15	34869;
16	48040;
17	51821;
18	53189;
19	42386;
20	43575;
21	53499;
22	53299;
23	39376;
24	42649;
25	45421;
26	41521;
27	40123;
28	40182;
29	45229;
30	44270;
31	51388;
32	51846;
33	54072;
34	52813;
35	48767;
36	45989;
37	43860;
38	51052;
39	45479;
40	40880;
41	47094;
42	50068;
43	42317;
44	42399;
45	35831;
46	56421;
47	52321;
48	46109;
49	50848;
50	52646;
Sheet2

Cell Formulas
Range	Formula
C2:C11	C2	=CONCAT(INDEX($A$2:$A$150000,SEQUENCE(10,,ROWS(C$2:C2)*10-9)))

For 4000 cells each time it would be

=CONCAT(INDEX($A$2:$A$150000,SEQUENCE(4000,,ROWS(C$2:C2)*4000-3999)))

 

[Fluff]

Another option if you have the Lambda function
=LET(Rws,ROUNDUP(COUNTA(A2:A150000)/4000,0),MAKEARRAY(Rws,1,LAMBDA(r,c,CONCAT(INDEX(A2:A150000,SEQUENCE(4000,,r*4000-3999))))))