# Arrange the numbers as per box range

#### motilulla

##### Well-known Member
Hello,

I need help to arrange 8 random number min = 1 max = 24 in the 6 boxes each box is filled with 4 numbers

For example in the cells C6:J6 I got 8 random numbers in ascending order i need to put them under each box with their corresponding value 2 & 3 go in the cell = M6, 5 & 7 go in the cell = N6, 13, 15 & 16 go in the cell = P6, 21 go in the cell = R6

With the same way row 7.... 8 & so on

For more detail the image is attached.

 * A B C D E F G H I J K L M N O P Q R S T 1 Box-1 Box-2 Box-3 Box-4 Box-5 Box-6 2 1 5 9 13 17 21 3 2 6 10 14 18 22 4 3 7 11 15 19 23 5 4 8 12 16 20 24 6 2 3 5 7 13 15 16 21 2 | 3 5 | 7 13 | 15 | 16 21 7 1 2 6 7 13 17 18 19 1 | 2 6 | 7 13 17 | 18 | 19 8 2 7 10 11 18 20 22 24 2 7 10 | 11 18 | 20 22 | 24 9 10 11

Thank you all.

I am using Excel 2000

Regards,
Moti

#### Attachments

• Rearrange Numbers.png
15.4 KB · Views: 8

#### Marc L

##### Well-known Member
So move C5:J5 to C4 'cause I can't work from any image but from the data sample you shared where C5:J5 is blank,​
the reason why it's better to use this forum tool XL2BB or link a sample workbook on a files host website …​
Update also your profile in order people see which Excel version you use …​

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### motilulla

##### Well-known Member
I can't work from any image​
Marc L, It is fair you are correct sorry for the incontinence here is the correct layout please consider and take a look again thank you.
the reason why it's better to use this forum tool XL2BB or link a sample workbook on a files host website …​
I can not because excel 2000 do no have this option
Update also your profile in order people see which Excel version you use …​
I cant there is no option to update my version excel 2000 that is why I write bottom of my each post

 * A B C D E F G H I J K L M N O P Q R S T 1 Box-1 Box-2 Box-3 Box-4 Box-5 Box-6 2 1 5 9 13 17 21 3 2 6 10 14 18 22 4 3 7 11 15 19 23 5 n1 n2 n3 n4 n5 n6 n7 n8 4 8 12 16 20 24 6 2 3 5 7 13 15 16 21 2|3 5|7 13|15|16 21 7 1 2 6 7 13 17 18 19 1|2 6|7 13 17|18|19 8 2 7 10 11 18 20 22 24 2 7 10|11 18|20 22|24 9 10 11

Kind Regards,
Moti

#### jasonb75

##### Well-known Member
I can not because excel 2000 do no have this option
That is not true, Excel 2000 supports add ins, although it might be the case that the XL2BB add in uses features that are not supported.
Excel 2000 also allows you to save workbooks If yo can save it then you can upload it to a fie share site.

Or maybe invest in a newer version that is more capable? With office 365 no vba is needed for such a simple task.

Book1
CDEFGHIJKLMNOPQR
1Box-1Box-2Box-3Box-4Box-5Box-6
2159131721
32610141822
43711151923
54812162024
62357131516212 | 35 | 7 13 | 15 | 16 21
712671317181926 | 7 1317 | 18 | 19
827101118202224 710 | 11 18 | 2022 | 24
Sheet1
Cell Formulas
RangeFormula
M6:R8M6=TEXTJOIN(" | ",1,IFERROR(IF(LOOKUP(\$C6:\$J6,M\$2:M\$5)=\$C6:\$J6,\$C6:\$J6,""),""))

#### motilulla

##### Well-known Member
That is not true, Excel 2000 supports add ins, although it might be the case that the XL2BB add in uses features that are not supported.
Excel 2000 also allows you to save workbooks If yo can save it then you can upload it to a fie share site.

Or maybe invest in a newer version that is more capable? With office 365 no vba is needed for such a simple task.

Book1
CDEFGHIJKLMNOPQR
1Box-1Box-2Box-3Box-4Box-5Box-6
2159131721
32610141822
43711151923
54812162024
62357131516212 | 35 | 7 13 | 15 | 16 21
712671317181926 | 7 1317 | 18 | 19
827101118202224 710 | 11 18 | 2022 | 24
Sheet1
Cell Formulas
RangeFormula
M6:R8M6=TEXTJOIN(" | ",1,IFERROR(IF(LOOKUP(\$C6:\$J6,M\$2:M\$5)=\$C6:\$J6,\$C6:\$J6,""),""))
jasonb75, formula is giving #NAME? error, , it is because "IFERROR" function does not work in Excel 2000.

As far as I have search a lot about Add-Ins did not find anything might be some know and help here to give some link.

Kind Regards,
Moti

#### Eric W

##### MrExcel MVP

Here is a formula, tested on Excel 2000. Long and awkward, but it seems to work:

Book2
ABCDEFGHIJKLMNOPQR
1Box-1Box-2Box-3Box-4Box-5Box-6
2159131721
32610141822
43711151923
54812162024
62357131516212|35|7 13|15|16 21
71267131718191|26|7 1317|18|19
8271011182022242710|11 18|2022|24
Sheet1
Cell Formulas
RangeFormula
M6:R8M6=SUBSTITUTE(IF(COUNTIF(M\$2:M\$5,\$C6),\$C6&"|","")&IF(COUNTIF(M\$2:M\$5,\$D6),\$D6&"|","")&IF(COUNTIF(M\$2:M\$5,\$E6),\$E6&"|","")&IF(COUNTIF(M\$2:M\$5,\$F6),\$F6&"|","")&IF(COUNTIF(M\$2:M\$5,\$G6),\$G6&"|","")&IF(COUNTIF(M\$2:M\$5,\$H6),\$H6&"|","")&IF(COUNTIF(M\$2:M\$5,\$I6),\$I6&"|","")&IF(COUNTIF(M\$2:M\$5,\$J6),\$J6&"|",""),"|","",MAX(1,SUMPRODUCT(COUNTIF(M\$2:M\$5,\$C6:\$J6))))

#### jasonb75

##### Well-known Member
formula is giving #NAME? error, , it is because "IFERROR" function does not work in Excel 2000.
Iferror and textjoin both require newer versions (minimum 2019) that formula was an example to demonstrate that it is much easier to perform such tasks with newer versions of excel than it is with outdated versions.
As far as I have search a lot about Add-Ins did not find anything might be some know and help here to give some link.
Click on the XL2BB link below and it will tell you how to install and use the add in, although it may be different for excel 2000. Detailed information specific to such an old version is going to be extremely difficult to find. Much of it will have been archived or deleted and search engines are likely to prioritise information for newer versions as being more relevant.

#### motilulla

##### Well-known Member

Here is a formula, tested on Excel 2000. Long and awkward, but it seems to work:

Book2
ABCDEFGHIJKLMNOPQR
1Box-1Box-2Box-3Box-4Box-5Box-6
2159131721
32610141822
43711151923
54812162024
62357131516212|35|7 13|15|16 21
71267131718191|26|7 1317|18|19
8271011182022242710|11 18|2022|24
Sheet1
Cell Formulas
RangeFormula
M6:R8M6=SUBSTITUTE(IF(COUNTIF(M\$2:M\$5,\$C6),\$C6&"|","")&IF(COUNTIF(M\$2:M\$5,\$D6),\$D6&"|","")&IF(COUNTIF(M\$2:M\$5,\$E6),\$E6&"|","")&IF(COUNTIF(M\$2:M\$5,\$F6),\$F6&"|","")&IF(COUNTIF(M\$2:M\$5,\$G6),\$G6&"|","")&IF(COUNTIF(M\$2:M\$5,\$H6),\$H6&"|","")&IF(COUNTIF(M\$2:M\$5,\$I6),\$I6&"|","")&IF(COUNTIF(M\$2:M\$5,\$J6),\$J6&"|",""),"|","",MAX(1,SUMPRODUCT(COUNTIF(M\$2:M\$5,\$C6:\$J6))))
Eric W, even it is an awkward but main part it worked, as I required that is more important for me.

I really value your help and time you spent working on this huge formula.

Good Luck have a great weekend.

Kind Regards,
Moti

#### motilulla

##### Well-known Member
Iferror and textjoin both require newer versions (minimum 2019) that formula was an example to demonstrate that it is much easier to perform such tasks with newer versions of excel than it is with outdated versions.

Click on the XL2BB link below and it will tell you how to install and use the add in, although it may be different for excel 2000. Detailed information specific to such an old version is going to be extremely difficult to find. Much of it will have been archived or deleted and search engines are likely to prioritise information for newer versions as being more relevant.
jasonb75, thank you taking a time and guiding all the process i went through but it seems

Extension. Xlam of the file XL2BB add in. is not compatible with xl2000.

Good Luck have a great weekend.

Kind Regards,
Moti

#### Marc L

##### Well-known Member
I cant there is no option to update my version excel 2000 that is why I write bottom of my each post
You did not understand as I was not stating to update Excel but your profile here within this forum ‼​

Replies
15
Views
160
Replies
3
Views
65
Replies
7
Views
92
Replies
1
Views
88
Replies
33
Views
987

1,141,293
Messages
5,705,532
Members
421,399
Latest member
hjweiss00

### 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.

### Which adblocker are you using?

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

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