Arrange the numbers as per box range

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,360
Office Version
  1. 2010
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: 10
Sort by date Sort by votes
​
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 …​
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Marc L said:
I can't work from any image​
Click to expand...
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.
Marc L said:
the reason why it's better to use this forum tool XL2BB or link a sample workbook on a files host website …​
Click to expand...
I can not because excel 2000 do no have this option
Marc L said:
Update also your profile in order people see which Excel version you use …​
Click to expand...
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
 
Upvote 0
motilulla said:
I can not because excel 2000 do no have this option
Click to expand...
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,""),""))
 
Upvote 0
jasonb75 said:
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,""),""))
Click to expand...
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
 
Upvote 0
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))))
 
Upvote 0
Solution
motilulla said:
formula is giving #NAME? error, , it is because "IFERROR" function does not work in Excel 2000.
Click to expand...
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.
motilulla said:
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 to expand...
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.
 
Upvote 0
Eric W said:
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))))
Click to expand...
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 :)
 
Upvote 0
jasonb75 said:
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.
Click to expand...
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 :)
 
Upvote 0
motilulla said:
I cant there is no option to update my version excel 2000 that is why I write bottom of my each post
Click to expand...
You did not understand as I was not stating to update Excel but your profile here within this forum ‼​
​
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Sort scripture references by chapter number
Replies
9
Views
214
Peter_SSs
Peter_SSs
K
  • Question
conditional formatting question
Replies
2
Views
146
kerm007
K
A
  • Question
Excel Formula.
Replies
1
Views
177
StephenCrump
S
P
  • Question
vba at each change
2
Replies
15
Views
669
kevin9999
K
J
  • Solved
Fill numbers based on Sum
Replies
7
Views
342
kevin9999
K

Forum statistics

Threads
1,214,424
Messages
6,119,400
Members
448,893
Latest member
AtariBaby

Share this page

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