Multiplying arrays

[scarper]

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CSTEVE%7E1.BAR%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-bidi-font-family:"Times New Roman"; mso-bidi-language:AR-SA;} @page Section1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Hi folks

I would like to multiply two one-dimensional horizontal arrays so that the first element of array1 is multiplied by each element of array2 and then the second element of array1 is multiplied by each element of array2 and so on..

To illustrate what i mean:

Array1: 2, 6, 10
Array2: 4, 7

I want to create an array3 so that
Array3: 2*4, 2*7, 6*4, 6*7, 10*4, 10*7

Is there a quick way of doing this?

Many thanks

 

[Andrew Poulsom]

Let's say A1:A3 contains the numbers 2, 6 and 10 and that B1:C1 contains 4 and 7. Select D1:E3, type:

=MMULT(A1:A3,B1:C1)

and press Ctrl+Shift+Enter. Result:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=64 align=right x:num="8">8 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num="14">14 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=right x:num="24">24 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num="42">42 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 align=right x:num="40">40 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 align=right x:num="70">70 </TD></TR></TBODY></TABLE>

 

[milkoholic]

First post in the forums.
I am no expert, but here you have a VBA-example how i did it.

Let's assume that your numbers are located in rows 1 and 2.
This code then enters the numbers in row 4.

Sub Multiply_Arrays()
Dim Arr1() As Integer, Arr2() As Integer, Arr3() As Integer
Dim a1 As Integer, a2 As Integer, a3 As Integer
Dim col As Integer, count As Integer
Dim rng As Range


a1 = WorksheetFunction.CountA(Range("1:1"))
a2 = WorksheetFunction.CountA(Range("2:2"))
a3 = a1 * a2

ReDim Arr1(a1)
ReDim Arr2(a2)
ReDim Arr3(a3)

col = 1

    For i = 1 To a1
    Arr1(i) = Cells(1, col)
    col = col + 1
    Next i
    
col = 1

    For i = 1 To a2
    Arr2(i) = Cells(2, col)
    col = col + 1
    Next i
    

        For i = 1 To a1
            For j = 1 To a2
                Arr3(count) = Arr1(i) * Arr2(j)
                count = count + 1
            Next j
        Next i
        
Set rng = Range(Cells(4, 1), Cells(4, a3))

rng.Value = Arr3

End Sub

 

[p45cal]

Sub blah4()
Arr1 = Array(2, 6, 10)
Arr2 = Array(4, 7)
Dim Arr3()
ReDim Arr3(1 To (UBound(Arr1) - LBound(Arr1) + 1) * (UBound(Arr2) - LBound(Arr2) + 1))
i = 1
For Each val1 In Arr1
  For Each val2 In Arr2
    Arr3(i) = val1 * val2
    i = i + 1
  Next val2
Next val1
End Sub

 

[p45cal]

to convert my last offering to a function that accepts two horizontal ranges (only ranges mind you, for the moment):

Function blah(Arr1, Arr2)
Dim Arr3()
ReDim Arr3(1 To Arr1.Count * Arr2.Count)
i = 1
For Each val1 In Arr1
  For Each val2 In Arr2
    Arr3(i) = val1 * val2
    i = i + 1
  Next val2
Next val1
blah = Arr3
End Function

and use in the worksheet by first selecting a horizontal range of cells 1 row high and as wide as the number of cells in the other two ranges multiplied together, and array-enter (ctrl+shift+enter, not just enter) the likes of the following:
=blah(A6:C6,A7:B7)

 

[scarper]

Thanks! The first example does what i need. Not quite ready the VBA stuff today but i'll give it a try. Thank you for your help