Linking List

[Bottle]

I have two lists of numbers one (432) and the other
(559), i would like to link each number in list one to
each number in list two, giving me in total 241488
different numbers.
Is there a formula i could run to do this?
Thanks

 

[JulieD]

How would you like them displayed? as excel is limited to 65,536 row?

 

[Frank Kabel]

Hi
not tested but try the following macro on a separate sheet

sub foo()
dim row_index as long
dim col_index as long
dim i as integer
dim j as integer
dim src_sheet as worksheet
dim tar_sheet as worksheet
row_index=1
col_index=1

set src_sheet=activeworkbook.worksheets("sheet1")
set tar_sheet=activeworkbook.worksheets("sheet2")
application.screenupdate=false
for i = 1 to 432
for j = 1 to 559
tar_sheet.cells(row_index,col_index).value =
src_sheet.cells(i,1).value*src_sheet.cells(j,1).value
row_index=row_index+1
if row_index=65000 then
row_index=1
col_index=col_index+1
end if
next
next
application.screenupdate=true
end sub

 

[Jason Morin]

You could write all possible combination to a text file
(called testfile.txt under C:\ in this example):

Sub AllCombinations()

'// Constructive criticism from VBA programmers welcome

Dim FSO As Object
Dim myFile As Object
Dim ws As Worksheet
Dim cell As Range
Dim rng1 As Range
Dim rng2 As Range
Dim i As Long
Dim j As Long

Set ws = Workbooks("Book2.xls").Sheets("Sheet1")
Set rng1 = ws.Range("A1:A" & Range("A1").End(xlDown).Row)
Set rng2 = ws.Range("B1:B" & Range("B1").End(xlDown).Row)
Set FSO = CreateObject("Scripting.FileSystemObject")
Set myFile = FSO.CreateTextFile("C:\testfile.txt", True)

For i = 1 To rng1.Rows.Count
For j = 1 To rng2.Rows.Count
myFile.WriteLine ws.Range("A" & i).Value & _
ws.Range("B" & j).Value
Next
Next

myFile.Close

End Sub

---
This assumes your worbook is called Book2.xls, and the
first list is in column A of Sheet1 and the second list
is in column B.

HTH
Jason
Atlanta, GA