Split 2D array into two equal arrays

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Split 2D array into two equal arrays

Post by YasserKhalil »

Hello everyone
Suppose I have a 2D array with 7 rows and three columns and I need to split this 2d array into two 2d arrayys
The first output array would be 4 rows and three columns and the second output array would be 3 rows and three columns
Is it possible to implement that into a function? I mean that the UDF that would be used return two arrays ...

User avatar
HansV
Administrator
Posts: 78240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Split 2D array into two equal arrays

Post by HansV »

Why not? It's easy enough to create the required loops.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Split 2D array into two equal arrays

Post by YasserKhalil »

I am talking about how to return two arrays from UDF, not about the loops.

User avatar
HansV
Administrator
Posts: 78240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Split 2D array into two equal arrays

Post by HansV »

You could return an array of arrays, but why would you want to do that?
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Split 2D array into two equal arrays

Post by YasserKhalil »

Thanks a lot. No specific reason (Just for curiosity to see if it is possible to return multi-arrays from UDF)
Can you show me simple example?

User avatar
HansV
Administrator
Posts: 78240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Split 2D array into two equal arrays

Post by HansV »

I think it's useless, but here is an example:

Code: Select all

Function ArrayOfArrays(a(), n As Long)
    Dim b(), c()
    Dim i As Long
    Dim j As Long
    ReDim b(LBound(a, 1) To LBound(a, 1) + n - 1, LBound(a, 2) To UBound(a, 2))
    For i = LBound(a, 1) To LBound(a, 1) + n - 1
        For j = LBound(a, 2) To UBound(a, 2)
            b(i, j) = a(i, j)
        Next j
    Next i
    ReDim b(LBound(a, 1) To UBound(a, 1) - (LBound(a, 1) + n - 1), LBound(a, 2) To UBound(a, 2))
    For i = LBound(a, 1) To UBound(a, 1) - (LBound(a, 1) + n - 1)
        For j = LBound(a, 2) To UBound(a, 2)
            b(i, j) = a(i + n, j)
        Next j
    Next i
    ArrayOfArrays = Array(a, b)
End Function
And an example of using the function:

Code: Select all

Sub Test()
    Dim a(), r
    Dim i As Long
    Dim j As Long
    Dim k As Long
    a = Range("A1:C7").Value
    r = ArrayOfArrays(a, 4)
    For k = 0 To 1
        For i = LBound(r(k), 1) To UBound(r(k), 1)
            For j = LBound(r(k), 2) To UBound(r(k), 2)
                Debug.Print k, i, j, r(k)(i, j)
            Next j
        Next i
    Next k
End Sub
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Split 2D array into two equal arrays

Post by YasserKhalil »

Can you explain to me why useless ..? and in that case what is the best approach?!!

User avatar
HansV
Administrator
Posts: 78240
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Split 2D array into two equal arrays

Post by HansV »

I don't see any real-world use for this.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Split 2D array into two equal arrays

Post by Doc.AElstein »

YasserKhalil wrote:
30 Oct 2020, 09:49
.. why useless
One possible reason why Yasser,
You know how to get the sub arrays in one code line:

Code: Select all

Sub second_output_array_would_be_3_rows_and_three_columns()  '   http://www.eileenslounge.com/viewtopic.php?f=30&t=35595
Dim a() As Variant, En As Long
 Let a() = Range("A1:C7").Value

Dim b() As Variant
 Let En = 4
 Let b() = Application.Index(a(), Evaluate("=ROW(" & En + 1 & ":" & UBound(a(), 1) & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "$")(1) & ")"))

' Or
 Let b() = Application.Index(Cells, Evaluate("=ROW(" & En + 1 & ":" & UBound(a(), 1) & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "$")(1) & ")"))

End Sub






Ref
https://www.excelforum.com/excel-new-us ... ost4571172
https://www.excelforum.com/tips-and-tut ... ost5408376
https://www.excelforum.com/excel-progra ... array.html
http://www.eileenslounge.com/viewtopic. ... 35#p271035
https://www.ozgrid.com/forum/index.php? ... ost1239241
https://eileenslounge.com/viewtopic.php ... c31#p27436
https://eileenslounge.com/viewtopic.php ... 84#p265384
Last edited by Doc.AElstein on 22 Nov 2020, 12:05, edited 3 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Split 2D array into two equal arrays

Post by YasserKhalil »

Thanks a lot but in fact I will use it for splitting a lot of arrays that will be preodcued
Generally, after testing I found that the second array is OK (3 rows) while the first array has (7 rows) and I expect (4 rows)

Code: Select all

    a = Range("A1").CurrentRegion.Value
    r = ArrayOfArrays(a, 4)
    Range("F1").Resize(UBound(r(0), 1), UBound(r(0), 2)).Value = r(0)
    Range("F10").Resize(UBound(r(1), 1), UBound(r(1), 2)).Value = r(1)

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Split 2D array into two equal arrays

Post by Doc.AElstein »

Code: Select all

Sub first_output_array_would_be_4_rows_and_three_columns()  '   http://www.eileenslounge.com/viewtopic.php?f=30&t=35595
Dim a() As Variant, En As Long
 Let a() = Range("A1:C7").Value

Dim c() As Variant
 Let En = 4
 Let c() = Application.Index(a(), Evaluate("=ROW(1:" & En & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "$")(1) & ")"))

' Or
 Let c() = Application.Index(Cells, Evaluate("=ROW(1:" & En & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "$")(1) & ")"))

End Sub

Code: Select all

Sub first_output_array_would_be_4_rows_and_three_columns__AND__second_output_array_would_be_3_rows_and_three_columns()  '   http://www.eileenslounge.com/viewtopic.php?f=30&t=35595
Dim a() As Variant, En As Long
 Let a() = Range("A1:C7").Value

Dim c() As Variant, b() As Variant
 Let En = 4
 
 Let b() = Application.Index(Cells, Evaluate("=ROW(" & En + 1 & ":" & UBound(a(), 1) & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "$")(1) & ")"))
 Let c() = Application.Index(Cells, Evaluate("=ROW(1:" & En & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "$")(1) & ")"))
End Sub

_._________________________________


YasserKhalil wrote:
30 Oct 2020, 11:53
... I will use it for splitting a lot of arrays that will
Maybe do it something like this

Code: Select all

Option Explicit
Dim c() As Variant, b() As Variant, a() As Variant
Sub first_output_array_would_be_En_rows_and_three_columns_second_output_array_would_be_En_minus_one_rows_and_three_columns(En As Long)   '   http://www.eileenslounge.com/viewtopic.php?f=30&t=35595
 Let b() = Application.Index(Cells, Evaluate("=ROW(" & En + 1 & ":" & UBound(a(), 1) & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "$")(1) & ")"))
 Let c() = Application.Index(Cells, Evaluate("=ROW(1:" & En & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "$")(1) & ")"))
End Sub
Sub Testit()
 Let a() = Range("A1:C7").Value: Call first_output_array_would_be_En_rows_and_three_columns_second_output_array_would_be_En_minus_one_rows_and_three_columns(4)
' Now look what cha  got in c() and b()
End Sub


Loop that idea for your lot of arrays.....

Code: Select all

Option Explicit
Dim c() As Variant, b() As Variant, a() As Variant
Sub first_output_array_would_be_En_rows_and_three_columns_second_output_array_would_be_En_minus_one_rows_and_three_columns(En As Long)   '   http://www.eileenslounge.com/viewtopic.php?f=30&t=35595
 Let b() = Application.Index(Cells, Evaluate("=ROW(" & En + 1 & ":" & UBound(a(), 1) & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "$")(1) & ")"))
 Let c() = Application.Index(Cells, Evaluate("=ROW(1:" & En & ")"), Evaluate("=COLUMN(A:" & Split(Cells(1, UBound(a(), 2)).Address, "$")(1) & ")"))
End Sub
Sub TestitLoopy()
Dim ARayOfARays() As Variant: Let ARayOfARays() = Array(Range("A1:C7").Value, Range("A1:C5").Value)
Dim StearIt As Variant
    For Each StearIt In ARayOfARays()
     Let a() = StearIt: Call first_output_array_would_be_En_rows_and_three_columns_second_output_array_would_be_En_minus_one_rows_and_three_columns(4)
    ' Now look what cha  got in c() and b()
    Next StearIt
End Sub
Last edited by Doc.AElstein on 30 Oct 2020, 12:44, edited 1 time in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Split 2D array into two equal arrays

Post by YasserKhalil »

Thanks a lot, Mr. Alan for this perfect solution.
In fact, what I am worried about is that the real data is too large and I am not sure if using the Application Index with evaluate will be able to deal with these large amounts.

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Split 2D array into two equal arrays

Post by Doc.AElstein »

I am not sure what the limits are.
Possibly row limit of about 65,000 with Index. ( The Evaluates are just getting indicia arrays, like {1, 2, 3, 4 ....} etc.... so that is less likely to be the limitation).
But it may all be influenced by your computer performance and many other things related to your Office version.

I don't have a lot of experience with large amounts of real data, so I am not sure of the real data limits...
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Split 2D array into two equal arrays

Post by YasserKhalil »

Thanks a lot anyway for the nice solution you offered. I really liked it so much as it is very compact and can be used in other issues. I will certainly make use of it.
Best Regards