Can this be solved with a formula?

djs6025

New Member
Joined
Feb 6, 2012
Messages
40
Office Version
  1. 2016
Platform
  1. Windows
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
1BXLT7GZBYL;
2BXLT7CTV96;
3BXLT7GD5Y7;
4BXLT7GVMLM;
5BXLT7J1JRR;
6BXLT7H77DB;
7BXLT7FM49S;
8BXLT7GKLC4;
9BXLT7J96KR;
10BXLT7G8YQN;
11BXLT7DXGN3;
12BXLT7H64V3;
13BXLT7FMLN4;
14BXLT7GX5DH;
Sheet1
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
In Excel 2016, you could use an approach like this:
MrExcel_20231017.xlsx
ABC
1BXLT7GZBYL;BXLT7GZBYL;BXLT7CTV96;BXLT7GD5Y7;BXLT7GVMLM;
2BXLT7CTV96;BXLT7J1JRR;BXLT7H77DB;BXLT7FM49S;BXLT7GKLC4;
3BXLT7GD5Y7;BXLT7J96KR;BXLT7G8YQN;BXLT7DXGN3;BXLT7H64V3;
4BXLT7GVMLM;BXLT7FMLN4;BXLT7GX5DH;
5BXLT7J1JRR;
6BXLT7H77DB;
7BXLT7FM49S;
8BXLT7GKLC4;
9BXLT7J96KR;
10BXLT7G8YQN;
11BXLT7DXGN3;
12BXLT7H64V3;
13BXLT7FMLN4;
14BXLT7GX5DH;
Sheet1
Cell Formulas
RangeFormula
C1:C4C1=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:
Excel Formula:
=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.
 
Last edited:
Upvote 0
Hoping you have at least office 2021 for this to work:

Cell Formulas
RangeFormula
E1E1=TEXTJOIN(,TRUE,INDIRECT(ADDRESS(1,1)&":"&ADDRESS(D1,1)))
C2:C18C2=C1+1
D2:D18D2=$D$1*C2
E3:E18E3=TEXTJOIN(,TRUE,INDIRECT(ADDRESS(D2+1,1)&":"&ADDRESS(D3,1)))
 
Upvote 1
Hoping you have at least office 2021 for this to work:

Cell Formulas
RangeFormula
E1E1=TEXTJOIN(,TRUE,INDIRECT(ADDRESS(1,1)&":"&ADDRESS(D1,1)))
C2:C18C2=C1+1
D2:D18D2=$D$1*C2
E3:E18E3=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
 
Upvote 0
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’)
 
Upvote 0
Another option for 365 that is not volatile
Excel Formula:
=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))
 
Upvote 0
I wish to reach this level of coding in the future.. just jaw dropping.
Another option for 365 that is not volatile
Excel Formula:
=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))
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,217
Members
453,024
Latest member
Wingit77

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