VBA to Separate Values

[montecarlo2012]

Hi.
Please, in case is possible, I have a dynamic array five columns, the first column is only two words alternated and the other four columns are numbers (grades).
what I need?, I need to separate the rows of math from the row of geography and convert in two different arrays.
Thank you for reading this.
[TABLE="width: 440"]
<tbody>[TR]
[TD]math[/TD]
[TD]40[/TD]
[TD]80[/TD]
[TD]20[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]geog[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]math[/TD]
[TD]79[/TD]
[TD]30[/TD]
[TD]19[/TD]
[TD]68[/TD]
[/TR]
[TR]
[TD]geog[/TD]
[TD]87[/TD]
[TD]40[/TD]
[TD]10[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]math[/TD]
[TD]65[/TD]
[TD]78[/TD]
[TD]91[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]geog[/TD]
[TD]32[/TD]
[TD]45[/TD]
[TD]58[/TD]
[TD]71[/TD]
[/TR]
[TR]
[TD]math[/TD]
[TD]21[/TD]
[TD]12[/TD]
[TD]30[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]geog[/TD]
[TD]54[/TD]
[TD]23[/TD]
[TD]80[/TD]
[TD]39[/TD]
[/TR]
[TR]
[TD]math[/TD]
[TD]54[/TD]
[TD]56[/TD]
[TD]58[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]geog[/TD]
[TD]87[/TD]
[TD]89[/TD]
[TD]91[/TD]
[TD]93[/TD]
[/TR]
[TR]
[TD]math[/TD]
[TD]19[/TD]
[TD]32[/TD]
[TD]45[/TD]
[TD]58[/TD]
[/TR]
[TR]
[TD]geog[/TD]
[TD]37[/TD]
[TD]65[/TD]
[TD]93[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]math[/TD]
[TD]77[/TD]
[TD]98[/TD]
[TD]91[/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]
So, I would like to separate math from geography. thanks.

 

[kweaver]

If your data is in A1:E13, you would fill across and down starting in G1 with:

=INDEX(A:A,ROW(A1)*2-1)

Fill that across from, say, G1:K1 and then down. Then the math is no longer found, if you've filled down all 13 rows, you'll get 0s.
You could remove them in the formula or format the cells not to show 0s.

In M1:Q1, a similar formula:

=INDEX(A:A,ROW(A1)*2)

Hope this helps or at least gives you an idea of an approach to get what you want.

 

[montecarlo2012]

Kweaver, I really Appreciate your input, Thank you. I am really interesting about to see how this kind of ideas are implemented on the Golden Language VBA.Thank you for your time. My apology.

 

[pgc01]

Hi

If I understand correctly this is a vba question.

There are no vba native functions to deal with splitting an array.

In the case you posted, what you can do is to use a worksheet function.

Assuming the name of your array is vArray, try:

,,,
Dim vArray1 As Variant, vArray2 As Variant
Dim lN As Long ' Number of elements in the array
...

lN = UBound(vArray) - LBound(vArray) + 1

vArray1 = Application.Index(vArray, Evaluate("2*row(1:" & ((lN + 1) \ 2) & ")-1"), Array(1, 2, 3, 4, 5))
vArray2 = Application.Index(vArray, Evaluate("2*row(1:" & (lN \ 2) & ")"), Array(1, 2, 3, 4, 5))
...

 

[kweaver]

A possible VBA could be:

Sub SeparateRows()
Dim LR As Long, zM As Integer, zG As Integer, i As Integer
LR = Cells(Rows.Count, "A").End(xlUp).Row
zM = 1
zG = 1
For i = 1 To LR
  If Range("A" & i) = "math" Then
    Range("S" & zM & ":W" & zM) = Range("A" & i & ":E" & i).Value
    zM = zM + 1
   Else ' Range("A" & i) = "geog" Then
    Range("Y" & zG & ":AC" & zG) = Range("A" & i & ":E" & i).Value
    zG = zG + 1
  End If
Next i
End Sub

Of course, adjust where you want the results (I put them in S:W and Y:AC)

 

[montecarlo2012]

Thank you pgc 01, It is good to know the limits of the Golden language VBA.

 

[montecarlo2012]

Mr. kweaver, Thank you again for your time.

 

[Rick Rothstein]

Hi

If I understand correctly this is a vba question.

There are no vba native functions to deal with splitting an array.

In the case you posted, what you can do is to use a worksheet function.

Assuming the name of your array is vArray, try:

,,,
Dim vArray1 As Variant, vArray2 As Variant
Dim lN As Long ' Number of elements in the array
...

lN = UBound(vArray) - LBound(vArray) + 1

vArray1 = Application.Index(vArray, Evaluate("2*row(1:" & ((lN + 1) \ 2) & ")-1"), [B][COLOR="#FF0000"]Array(1, 2, 3, 4, 5)[/COLOR][/B])
vArray2 = Application.Index(vArray, Evaluate("2*row(1:" & (lN \ 2) & ")"), [B][COLOR="#FF0000"]Array(1, 2, 3, 4, 5)[/COLOR][/B])
...

If you wanted to save a small amount of typing, you could replace what I highlighted in red with this...

[{1,2,3,4,5}]

 

[montecarlo2012]

kweaver The part I like most about your answer is that I can see the clear logical path you took to resolve the problem, and use just a few line as possible, exactly for the Chapter I am now about FOR NEXT loop. Great job.

 

[montecarlo2012]

Rick Rothstein Thanks. Your interventions always are a real [FONT=&quot]enlightening[/FONT]