Consolidating two arrays into one & output to range

jmichaelp · Aug 18, 2003

Hi, I have two ranges (5k cells each) that I want to read into two arrays, combine and then output to a range. The combined array should contain the first cell of range1 and each cell of range2, the second cell of range1 and each cell of range2, etc. as follows:
AX
AY
AZ
BX
BY
BZ
CX
CY
CZ

The following code works fine when outputting to a msgbox, but not to a range. Instead I get:
AZ
BZ
CZ

It's been 3-4 years since I wrote any code! Can someone help? Thanks!

Code:

Sub KData()
  Dim vaSData As Variant
  Dim vaCData As Variant
  Dim vaSData1 As Variant
  Dim i As Long
  Dim i1 As Long
  vaSData = Range("A10:b16").Value
  vaSData1 = Range("c10:c16").Value
  ReDim vaCData(1 To UBound(vaSData, 1), 1 To 2)
  For i = 1 To UBound(vaSData, 1)
       For i1 = 1 To UBound(vaSData1, 1)
            'MsgBox vaSData(i, 1)
            'MsgBox vaSData1(i1, 1)
            vaCData(i, 1) = vaSData(i, 1)
            vaCData(i, 2) = vaSData1(i1, 1)
       Next i1
  Next i
 Range("f1").Resize(UBound(vaSData, 1), 2).Value = vaCData
 End Sub

NateO · Aug 18, 2003

Hello, and welcome to MrExcel.com.

As I cannot tell exactly what you're looking for based on your post, I will attempt to address your quandary via a brute force method. I suspect the 2nd animal is what you're looking for, but I could also picture your response being the first, or even C: None of the above.

Option Explicit

Sub KData()
Dim vaSdata As Variant, vaCData As Variant, vaSData1 As Variant
Dim i As Long, z As Long
vaSdata = [A10:b16]
vaSData1 = Range("c10:c16").Value
ReDim vaCData(1 To UBound(vaSdata) * 2, 1 To 2)
For i = 1 To UBound(vaSdata) * 2
 z = -(i > UBound(vaSdata))
 vaCData(i, 1) = vaSdata((i - z * 7), z + 1)
 vaCData(i, 2) = vaSData1((i - z * 7), 1)
Next i
[f1].Resize(UBound(vaCData), 2) = vaCData
End Sub

Sub KData2()
Dim vaSdata As Variant, vaCData As Variant, vaSData1 As Variant
Dim i As Long, z As Long
vaSdata = [A10:b16].Value
vaSData1 = [c10:c16].Value
ReDim vaCData(1 To UBound(vaSdata), 1 To 2)
For i = 1 To UBound(vaSdata) * 2
 z = -(i > UBound(vaSdata))
 vaCData((i - z * 7), z + 1) = _
 vaSdata((i - z * 7), z + 1) & ChrW$(32) & _
 vaSData1((i - z * 7), 1)
Next i
[f1].Resize(UBound(vaCData), 2) = vaCData
End Sub

Bon chance.

jmichaelp · Aug 18, 2003

Nate, thanks for the quick reply!

Unfortunately, I ended up with answer "C" - none of the above

What I'm looking to do is to get all combinations of the two input ranges, based on the first column of each of those ranges. Then output all of those combinations to another range of columns.

In other words, I'm looking to take what would otherwise be a matrix of range1 vs. range2 and put it in columnar format

Here's how I would do it with non-array code. This code would be too slow to execute for the 50-100 comparisons I need to make, each with thousands of lines of all of the different combinations:

Code:

Sub test()
Dim c As Range
Dim d As Range
Dim lastplc
lastplc = Application.CountA(ActiveSheet.Range("f:f")) + 1
For Each c In Range("A10:A16")
    lastplc = lastplc
    For Each d In Range("c10:c16")
        Cells(lastplc, 7) = c.Value
        Cells(lastplc, 8) = d.Value
        lastplc = lastplc + 1
    Next d
Next c
End Sub

Of course, I'll also want to do some calculations on all combinations of the columns in range1 and range2 but once I figure out how to combine and output the primary columns using an array the rest should be easy.

Thanks!

NateO · Aug 18, 2003

Well, you're welcome, too bad we didn't get it. Do you mind if I ask as to how/why Animal A didn't do the trick?

I'm still not exactly clear on the issue, A gives your 2d 1 to x, 1 to 2 array. Do you want 1 to x, 1 to 1? If so, it could be a combo of Animal A & B, leading to one column of concatenated data. But some technical clarification would help at this point.

bat17 · Aug 19, 2003

How about:-

Sub KData6()
Dim vaAData As Variant, vaBdata As Variant, vaSData As Variant
Dim i As Long, j As Long, z As Long
Dim lngRows As Long, lngz As Long
vaAData = [a10:b16]
vaBdata = [c10:c16]
lngRows = UBound(vaAData)
lngz = lngRows / 2
z = 0
ReDim vaSData(1 To 2 * UBound(vaAData) ^ 2, 1 To 2)
lngz = UBound(vaSData) / 2
For i = 1 To lngRows
For j = 1 To lngRows
z = z + 1
vaSData(z, 1) = vaAData(i, 1)
vaSData(z, 2) = vaBdata(j, 1)
vaSData(z + lngz, 1) = vaAData(i, 2)
vaSData(z + lngz, 2) = vaBdata(j, 1)
Next j
Next i
Range("f1").Resize(UBound(vaSData), 2) = vaSData
End Sub

jmichaelp · Aug 19, 2003

bat17, cool that works as I envisioned!

Now I just have to understand your code so I can use it on the other columns of my data set

Thanks for your and Nate's help!

bat17 · Aug 20, 2003

I added the
vaSData(z + lngz, 1) = vaAData(i, 2)
to avoid having to use consecutive loops but thinking about it, it will jump a row if there is an even amount of data!

jmichaelp · Aug 20, 2003

Thanks, bat. I've got it figured out and now have the remainder of the array elements calculated out. I appreciate your help!

Consolidating two arrays into one & output to range

jmichaelp

Board Regular

Excel Facts

NateO

Legend

jmichaelp

Board Regular

NateO

Legend

bat17

Well-known Member

jmichaelp

Board Regular

bat17

Well-known Member

jmichaelp

Board Regular

Similar threads

Forum statistics

Share this page

Consolidating two arrays into one & output to range

Board Regular

Excel Facts

Legend

Board Regular

Legend

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock