How can i build up a Matrix in EXCEl ?

[Guest]

Hello i need urgently help:

how can i build a Matrix in Excel combining 250 items (ex. airports) with
250 items (ex. airports)?

i schould create a row with the 62500 possible combinations... there must be
a formula to make this autmatically.

ex. ZHR JFK ATL ....
ZHR
JFK
ATL
.....
And then I should get a list in one row :


ZHR-JFK
ZHR-ATL
......
JFL-ZHR
JFK-ATL
.....
ATL-ZHR
ATL-JFK

thank you very much for your help.

regards

Mischa

 

[Jim Cone]

Mischa,

You can use VBA code.
This assumes the airport lists are in column A and Column B and that
the combined list will go in column C.
It takes about 30 seconds to run.
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'----------------------------------
Sub MixAndMatchAirportCodes()
Dim rngOne As Excel.Range
Dim rngTwo As Excel.Range
Dim rng1 As Excel.Range
Dim rng2 As Excel.Range
Dim lngN As Long
Const str_dash As String = " - "

Set rngOne = Range("A1:A250")
Set rngTwo = Range("B1:B250")
lngN = 1
Application.ScreenUpdating = False
For Each rng1 In rngOne
For Each rng2 In rngTwo
Cells(lngN, 3).Value = rng1.Value & str_dash & rng2.Value
lngN = lngN + 1
Next
Next
Application.ScreenUpdating = True
Set rngOne = Nothing
Set rngTwo = Nothing
End Sub
'------------------------------


"Mischa Sollberger" <Mischa (e-mail address removed)>
wrote in message...
Hello i need urgently help:

how can i build a Matrix in Excel combining 250 items (ex. airports) with
250 items (ex. airports)?
i schould create a row with the 62500 possible combinations... there must be
a formula to make this autmatically.
ex. ZHR JFK ATL ....
ZHR
JFK
ATL
.....
And then I should get a list in one row :

ZHR-JFK
ZHR-ATL
......
JFL-ZHR
JFK-ATL
.....
ATL-ZHR
ATL-JFK

thank you very much for your help.
regards
Mischa