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

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,548
Office Version
  1. 365
Platform
  1. Windows
See if this works. I doubt that many people on here (if any) will be able to test in excel 2000 so it will likely involve an amount of trial and error.
Note that you will need to add the vba code before entering the formula.
VBA Code:
Function aconcat(a As Variant, Optional sep As String = "") As String
    ' Harlan Grove, Mar 2002
    ' With a minor edit to ignore blanks
Dim y As Variant
If TypeOf a Is Range Then
    For Each y In a.Cells
        If Len(y.Value) > 0 Then aconcat = aconcat & y.Value & sep
    Next y
ElseIf IsArray(a) Then
    For Each y In a
        If Len(y) > 0 Then aconcat = aconcat & y & sep
    Next y
Else
    aconcat = aconcat & a & sep
End If
    If Len(aconcat) > 0 Then aconcat = Left(aconcat, Len(aconcat) - Len(sep)) Else aconcat = ""
End Function
Book1
CDEFGHIJKLMNOPQR
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
Sheet2
Cell Formulas
RangeFormula
M6:R8M6=aconcat(IFERROR(IF(LOOKUP($C6:$J6,M$2:M$5)=$C6:$J6,$C6:$J6,""),"")," | ")
Press CTRL+SHIFT+ENTER to enter array formulas.
 

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,012
See if this works. I doubt that many people on here (if any) will be able to test in excel 2000 so it will likely involve an amount of trial and error.
Note that you will need to add the vba code before entering the formula.
22 | 24[/XD][/XR][/RANGE]
Cell Formulas
RangeFormula
M6:R8M6=aconcat(IFERROR(IF(LOOKUP($C6:$J6,M$2:M$5)=$C6:$J6,$C6:$J6,""),"")," | ")
Press CTRL+SHIFT+ENTER to enter array formulas.
jasonb75, I install the function as per your instruction after i applied a formula got a #VALUE! error, it is because "IFERROR" function does not work in Excel 2000.

I appreciate your help and time you took to make a function.

One question instead function it can't be made macro?

Kind Regards,
Moti
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,248
Office Version
  1. 2010
Platform
  1. Windows
Hello, a VBA demonstration for starters :​
VBA Code:
Sub Demo1()
          Dim W, C%, V(), R&, X, Y
    With [M1].CurrentRegion.Rows("2:5").Columns
        ReDim W(1 To .Count)
        For C = 1 To .Count:  W(C) = Application.Transpose(.Item(C)):  Next
    End With
    With [C6].CurrentRegion.Rows
        ReDim V(1 To .Count, 1 To UBound(W))
        For R = 1 To .Count
            X = Application.Index(.Item(R).Value2, 1, 0)
        For C = 1 To UBound(W)
            Y = Filter(Application.IfError(Application.Match(W(C), X, 0), False), False, False)
            If UBound(Y) > -1 Then V(R, C) = Join(Application.Index(X, 1, Y), "|")
        Next C, R
            [M6].Resize(.Count, UBound(W)).Value2 = V
    End With
End Sub
 

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,012

ADVERTISEMENT

Hello, a VBA demonstration for starters :​
VBA Code:
Sub Demo1()
          Dim W, C%, V(), R&, X, Y
    With [M1].CurrentRegion.Rows("2:5").Columns
        ReDim W(1 To .Count)
        For C = 1 To .Count:  W(C) = Application.Transpose(.Item(C)):  Next
    End With
    With [C6].CurrentRegion.Rows
        ReDim V(1 To .Count, 1 To UBound(W))
        For R = 1 To .Count
            X = Application.Index(.Item(R).Value2, 1, 0)
        For C = 1 To UBound(W)
            Y = Filter(Application.IfError(Application.Match(W(C), X, 0), False), False, False)
            If UBound(Y) > -1 Then V(R, C) = Join(Application.Index(X, 1, Y), "|")
        Next C, R
            [M6].Resize(.Count, UBound(W)).Value2 = V
    End With
End Sub
Marc L, After running a macro I get highlighted the line below in colour yellow wit error '438'

Please can you take a look?

I appreciate your help and time you took to build a macro.

VBA Code:
Y = Filter(Application.IfError(Application.Match(W(C), X, 0), False), False, False)

Kind Regards,
Moti
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,248
Office Version
  1. 2010
Platform
  1. Windows
My demonstration revamped for Excel versions prior to 2007 version :​
VBA Code:
Sub Demo1()
          Dim W, C%, V(), R&, X, Y, K%
    With [M1].CurrentRegion.Rows("2:5").Columns
        ReDim W(1 To .Count)
        For C = 1 To .Count:  W(C) = Application.Transpose(.Item(C)):  Next
    End With
    With [C6].CurrentRegion.Rows
        ReDim V(1 To .Count, 1 To UBound(W))
        For R = 1 To .Count
            X = Application.Index(.Item(R).Value2, 1, 0)
        For C = 1 To UBound(W)
            Y = Application.Match(W(C), X, 0)
        For K = 1 To UBound(Y)
            If IsError(Y(K)) Then Y(K) = False
        Next K
            Y = Filter(Y, False, False)
            If UBound(Y) > -1 Then V(R, C) = Join(Application.Index(X, 1, Y), "|")
        Next C, R
            [M6].Resize(.Count, UBound(W)).Value2 = V
    End With
End Sub
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,248
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

A little optimization :​
VBA Code:
Sub Demo1r()
          Dim W, C%, V(), R&, X, K%
    With [M1].CurrentRegion.Rows("2:5").Columns
        ReDim W(1 To .Count)
        For C = 1 To .Count:  W(C) = Application.Transpose(.Item(C)):  Next
    End With
    With [C6].CurrentRegion.Rows
        ReDim V(1 To .Count, 1 To UBound(W))
        For R = 1 To .Count
        For C = 1 To UBound(W)
            X = Application.Match(W(C), .Item(R), 0)
        For K = 1 To UBound(X)
            If IsError(X(K)) Then X(K) = False
        Next K
            X = Filter(X, False, False)
            If UBound(X) > -1 Then V(R, C) = Join(Application.Index(.Item(R), 1, X), "|")
        Next C, R
            [M6].Resize(.Count, UBound(W)).Value2 = V
    End With
End Sub
 

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,012
My demonstration revamped for Excel versions prior to 2007 version :​
VBA Code:
Sub Demo1()
          Dim W, C%, V(), R&, X, Y, K%
    With [M1].CurrentRegion.Rows("2:5").Columns
        ReDim W(1 To .Count)
        For C = 1 To .Count:  W(C) = Application.Transpose(.Item(C)):  Next
    End With
    With [C6].CurrentRegion.Rows
        ReDim V(1 To .Count, 1 To UBound(W))
        For R = 1 To .Count
            X = Application.Index(.Item(R).Value2, 1, 0)
        For C = 1 To UBound(W)
            Y = Application.Match(W(C), X, 0)
        For K = 1 To UBound(Y)
            If IsError(Y(K)) Then Y(K) = False
        Next K
            Y = Filter(Y, False, False)
            If UBound(Y) > -1 Then V(R, C) = Join(Application.Index(X, 1, Y), "|")
        Next C, R
            [M6].Resize(.Count, UBound(W)).Value2 = V
    End With
End Sub
Marc L, Yes this worked fine only it is placing result starting from M7 instead of M6 i played a bit with the line below but cannot get it sort out. Could you tell me how can which line has to be altered?

I appreciate your help and time you took to build a macro.

VBA Code:
[M6].Resize(.Count, UBound(W)).Value2 = V

Good Luck have a nice weekend.

Kind Regards,
Moti :)
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,248
Office Version
  1. 2010
Platform
  1. Windows
As obviously this codeline places the result to cell M6 so it means there is no match in C6:J6 …​
 
Last edited:

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,012
As obviously this codeline places the result to cell M6 so it means there is no match in C6:J6 …​
Marc L, Please see the image attached why i am getting result starting from M7 ?
 

Attachments

  • Rearrange Numbers1.png
    Rearrange Numbers1.png
    15.9 KB · Views: 3

Forum statistics

Threads
1,141,575
Messages
5,707,173
Members
421,495
Latest member
jono_oh

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