Multiplying arrays

scarper

New Member
Joined
Jul 20, 2010
Messages
3
****** 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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Jul 21, 2010
Messages
61
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.

Code:
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

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
Code:
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

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
to convert my last offering to a function that accepts two horizontal ranges (only ranges mind you, for the moment):
Code:
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

New Member
Joined
Jul 20, 2010
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,686
Messages
5,597,536
Members
414,153
Latest member
SandraC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top