Arrange the numbers as per box range

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,012
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.

*ABCDEFGHIJKLMNOPQRST
1Box-1Box-2Box-3Box-4Box-5Box-6
2159131721
32610141822
43711151923
54812162024
62357131516212 | 35 | 713 | 15 | 1621
71267131718191 | 26 | 71317 | 18 | 19
8271011182022242710 | 1118 | 2022 | 24
9
10
11

Thank you all.

I am using Excel 2000

Regards,
Moti
 

Attachments

  • Rearrange Numbers.png
    Rearrange Numbers.png
    15.4 KB · Views: 8

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,240
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Feb 13, 2008
Messages
2,012
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
I appreciate your help

*ABCDEFGHIJKLMNOPQRST
1Box-1Box-2Box-3Box-4Box-5Box-6
2159131721
32610141822
43711151923
5n1n2n3n4n5n6n7n84812162024
62357131516212|35|713|15|1621
71267131718191|26|71317|18|19
8271011182022242710|1118|2022|24
9
10
11

Kind Regards,
Moti
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,538
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 13, 2008
Messages
2,012
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.

I appreciate your help

Kind Regards,
Moti
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,406

ADVERTISEMENT

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))))
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,538
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 13, 2008
Messages
2,012

ADVERTISEMENT

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
Joined
Feb 13, 2008
Messages
2,012
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
Joined
Apr 5, 2021
Messages
1,240
Office Version
  1. 2010
Platform
  1. Windows
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 ‼​
 

Forum statistics

Threads
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.
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
Top