Can this be solved with a formula?

[djs6025]

Hi gang,
I have a spreadsheet that is 50,371 part #'s long. The part #'s are in cells A1 through A50371 (below is an example). If possible, I need a formula that would combine the part #'s into 1 cell (up to 2,900 part #'s per cell). Since I have 50,371 part #'s with a max limit of 2900 part #'s per cell we should have at least 18 cells with the results. The results would look like this and be in any cell "BXLT7GZBYL;BXLT7CTV96;BXLT7GD5Y7;BXLT7GVMLM;BXLT7J1JRR;BXLT7H77DB;BXLT7FM49S;BXLT7GKLC4;BXLT7J96KR;BXLT7G8YQN;BXLT7DXGN3;BXLT7H64V3;BXLT7FMLN4;BXLT7GX5DH;". Hopefully, I've explained this well enough. Thanks in advance for your help!

Thanks,
Dan

All+Listings+Report+10-19-2023.txt
	A
1	BXLT7GZBYL;
2	BXLT7CTV96;
3	BXLT7GD5Y7;
4	BXLT7GVMLM;
5	BXLT7J1JRR;
6	BXLT7H77DB;
7	BXLT7FM49S;
8	BXLT7GKLC4;
9	BXLT7J96KR;
10	BXLT7G8YQN;
11	BXLT7DXGN3;
12	BXLT7H64V3;
13	BXLT7FMLN4;
14	BXLT7GX5DH;
Sheet1

 

[KRice]

In Excel 2016, you could use an approach like this:

MrExcel_20231017.xlsx
	A	B	C
1	BXLT7GZBYL;		BXLT7GZBYL;BXLT7CTV96;BXLT7GD5Y7;BXLT7GVMLM;
2	BXLT7CTV96;		BXLT7J1JRR;BXLT7H77DB;BXLT7FM49S;BXLT7GKLC4;
3	BXLT7GD5Y7;		BXLT7J96KR;BXLT7G8YQN;BXLT7DXGN3;BXLT7H64V3;
4	BXLT7GVMLM;		BXLT7FMLN4;BXLT7GX5DH;
5	BXLT7J1JRR;
6	BXLT7H77DB;
7	BXLT7FM49S;
8	BXLT7GKLC4;
9	BXLT7J96KR;
10	BXLT7G8YQN;
11	BXLT7DXGN3;
12	BXLT7H64V3;
13	BXLT7FMLN4;
14	BXLT7GX5DH;
Sheet1

Cell Formulas
Range	Formula
C1:C4	C1	=CONCAT(INDEX(A$1:A$16,4*(ROWS(C$1:C1)-1)+ROW($1:$4)))

Note, the INDEX(A$1:A$16 reference needs to be a multiple of the number of items that should be concatenated in each cell, otherwise an error will be generated in the last concatenated cell. Also, for your case, this part of the formula... (4*(ROWS(C$1:C1)-1)+ROW($1:$4)) ...needs to be adjusted to reflect the number of items that should be concatenated.. In this example, we're concatenating every 4 rows, so a 4 is the multiplier and ROW($1:$4) is the last term.

So unconfirmed, but I think you might try:

=CONCAT(INDEX(A$1:A$52560,2920*(ROWS(C$1:C1)-1)+ROW($1:$2920)))

...and this assumes the cells below the end of your data (below 50371) are blank so that nothing else is concatenated in the last combined result.

 

[Armstrong_N14]

Hoping you have at least office 2021 for this to work:

Book2
	A	B	C	D	E
1	BXLT7GZBYL;		1	2900	BXLT7GZBYL;BXLT7CTV96;BXLT7GD5Y7;BXLT7GVMLM;BXLT7J1JRR;BXLT7H77DB;BXLT7FM49S;BXLT7GKLC4;BXLT7J96KR;BXLT7G8YQN;BXLT7DXGN3;BXLT7H64V3;BXLT7FMLN4;BXLT7GX5DH;asdasdasd
2	BXLT7CTV96;		2	5800
3	BXLT7GD5Y7;		3	8700
4	BXLT7GVMLM;		4	11600
5	BXLT7J1JRR;		5	14500
6	BXLT7H77DB;		6	17400
7	BXLT7FM49S;		7	20300
8	BXLT7GKLC4;		8	23200
9	BXLT7J96KR;		9	26100
10	BXLT7G8YQN;		10	29000
11	BXLT7DXGN3;		11	31900
12	BXLT7H64V3;		12	34800
13	BXLT7FMLN4;		13	37700
14	BXLT7GX5DH;		14	40600
15			15	43500
16			16	46400
17			17	49300
18			18	52200
Sheet1

Cell Formulas
Range	Formula
E1	E1	=TEXTJOIN(,TRUE,INDIRECT(ADDRESS(1,1)&":"&ADDRESS(D1,1)))
C2:C18	C2	=C1+1
D2:D18	D2	=$D$1*C2
E3:E18	E3	=TEXTJOIN(,TRUE,INDIRECT(ADDRESS(D2+1,1)&":"&ADDRESS(D3,1)))

 

[djs6025]

Hoping you have at least office 2021 for this to work:

Book2
	A	B	C	D	E
1	BXLT7GZBYL;		1	2900	BXLT7GZBYL;BXLT7CTV96;BXLT7GD5Y7;BXLT7GVMLM;BXLT7J1JRR;BXLT7H77DB;BXLT7FM49S;BXLT7GKLC4;BXLT7J96KR;BXLT7G8YQN;BXLT7DXGN3;BXLT7H64V3;BXLT7FMLN4;BXLT7GX5DH;asdasdasd
2	BXLT7CTV96;		2	5800
3	BXLT7GD5Y7;		3	8700
4	BXLT7GVMLM;		4	11600
5	BXLT7J1JRR;		5	14500
6	BXLT7H77DB;		6	17400
7	BXLT7FM49S;		7	20300
8	BXLT7GKLC4;		8	23200
9	BXLT7J96KR;		9	26100
10	BXLT7G8YQN;		10	29000
11	BXLT7DXGN3;		11	31900
12	BXLT7H64V3;		12	34800
13	BXLT7FMLN4;		13	37700
14	BXLT7GX5DH;		14	40600
15			15	43500
16			16	46400
17			17	49300
18			18	52200
Sheet1

Cell Formulas
Range	Formula
E1	E1	=TEXTJOIN(,TRUE,INDIRECT(ADDRESS(1,1)&":"&ADDRESS(D1,1)))
C2:C18	C2	=C1+1
D2:D18	D2	=$D$1*C2
E3:E18	E3	=TEXTJOIN(,TRUE,INDIRECT(ADDRESS(D2+1,1)&":"&ADDRESS(D3,1)))

That worked perfectly! I had to upgrade to 365 but I was long overdue. Thanks so much for your help!

Cheers,
Dan

 

[Fluff]

I had to upgrade to 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

Another option for 365 that is not volatile

=LET(f,FILTER(A1:A60000,A1:A60000<>""),DROP(REDUCE("",SEQUENCE(CEILING(ROWS(f)/2900,1),,,2900),LAMBDA(x,y,VSTACK(x,CONCAT(CHOOSEROWS(A1:A60000,SEQUENCE(2900,,y)))))),1))

 

[Armstrong_N14]

I wish to reach this level of coding in the future.. just jaw dropping.

Another option for 365 that is not volatile

=LET(f,FILTER(A1:A60000,A1:A60000<>""),DROP(REDUCE("",SEQUENCE(CEILING(ROWS(f)/2900,1),,,2900),LAMBDA(x,y,VSTACK(x,CONCAT(CHOOSEROWS(A1:A60000,SEQUENCE(2900,,y)))))),1))