Split 2D array into two equal arrays
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Split 2D array into two equal arrays
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 ...
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 ...
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Split 2D array into two equal arrays
Why not? It's easy enough to create the required loops.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Split 2D array into two equal arrays
I am talking about how to return two arrays from UDF, not about the loops.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Split 2D array into two equal arrays
You could return an array of arrays, but why would you want to do that?
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Split 2D array into two equal arrays
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?
Can you show me simple example?
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Split 2D array into two equal arrays
I think it's useless, but here is an example:
And an example of using the function:
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
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
Hans
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Split 2D array into two equal arrays
Can you explain to me why useless ..? and in that case what is the best approach?!!
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Split 2D array into two equal arrays
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
You can find me at DocAElstein also
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Split 2D array into two equal arrays
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)
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)
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Split 2D array into two equal arrays
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
_._________________________________
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
You can find me at DocAElstein also
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Split 2D array into two equal arrays
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.
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.
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Split 2D array into two equal arrays
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...
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
You can find me at DocAElstein also
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Split 2D array into two equal arrays
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
Best Regards