Help on question

QuestionEdit

This is Rakesh from INDIA.

I have a following data,

EU1 = BOM,COK,MAA,DEL

ME1 = DOH,MCT,KWI,SAH

I have 'EU1' in cell A1 and 'ME1' in cell B1 and value $ 350 in C1 that mean from each city of EU1 to each city of ME1 the airfare is $ 350

for this task we need to make the tamplet as below

BOM DOH 350 BOM MCT 350 BOM KWI 350 BOM SAH 350

COK DOH 350 COK MCT 350 COK KWI 350 COK SAH 350

MAA DOH 350 MAA MCT 350 MAA KWI 350 MAA SAH 350

DEL DOH 350 DEL MCT 350 DEL KWI 350 DEL SAH 350

as like EU1 and ME1 we have EU2,EU3,EU4 and ME1,ME2,ME3 with different groups of cities and with different fare value

is there any formula or macro by which i can make tamplete as shown above for "EU2,EU3,EU4 and ME1,ME2,ME3"

Hope i explain my question..

Thanks in advenace""

AnswerEdit

Rakesh,

You could certainly do it with a macro. However you don't say where you want the information, or where the these equivalencies are:

EU1 = BOM,COK,MAA,DEL

ME1 = DOH,MCT,KWI,SAH

assume

EU1 is in A1 and ME1 is in B1 and 350 is in C1 and the other codes are in columns A and B and th price in column C below it and these are all in sheet1 and you want the table in sheet2

Sub BuildTable() Dim v(1 To 8), v1(1 To 8), vv1, vv2 Dim sh1 As Worksheet, sh2 As Worksheet Dim cst As Long, idex1 As Long, idex2 As Long, i As Long, j As Long Dim s1 As String, s2 As String Dim r1 As Range, rw As Long, cell As Range

v(1) = "EU1" v(2) = "EU2" v(3) = "EU3" v(4) = "EU4" v(5) = "ME1" v(6) = "ME2" v(7) = "ME3" v(8) = "ME4"

' fill arrays with cities (use dummy values where you have ' not provided a value v1(1) = Array("BOM", "COK", "MAA", "DEL") ' < cities you provided v1(2) = Array("ABC", "EFG", "HIJ", "KLM") v1(3) = Array("NOP", "QRS", "TUV", "WXY") v1(4) = Array("A1A", "A1B", "A2C", "A2D") v1(5) = Array("DOH", "MCT", "KWI", "SAH") ' < cities you provided v1(6) = Array("M2A", "M2B", "M2C", "M2D") v1(7) = Array("M2E", "M2F", "M2G", "M2H") v1(8) = Array("M2I", "M2J", "M2K", "M2L")

Set sh1 = Worksheets("Sheet1") Set sh2 = Worksheets("Sheet2")

Set r1 = sh1.Range("A1", sh1.Cells(Rows.Count, "A").End(xlUp)) For Each cell In r1

 s1 = cell.Value
 s2 = cell.Offset(0, 1).Value
 cst = cell.Offset(0, 2).Value
 For i = 1 To 8
   If v(i) = s1 Then idex1 = i
   If v(i) = s2 Then idex2 = i
 Next
 vv1 = v1(idex1)
 vv2 = v1(idex2)
 rw = sh2.Cells(sh2.Rows.Count, "A").End(xlUp).Row
 If sh2.Cells(rw, 1) <> "" Then rw = rw + 1
 For i = LBound(vv1) To UBound(vv1)
   For j = LBound(vv2) To UBound(vv2)
      sh2.Cells(rw, "A").Value = vv1(i)
      sh2.Cells(rw, "B").Value = vv2(j)
      sh2.Cells(rw, "C").Value = cst
      rw = rw + 1
   Next j
 Next i

Next End Sub

so with this starting in A1 of sheet1

EU1 ME1 350 EU2 ME2 450 EU3 ME3 500 EU4 ME4 550

the code produced the

64 row table identical to what you show (but including the EU2, ME2, . . . additional cities).

you can replace the array elements with your actuall cities. The arrays are not restricted to just 4 cities. You can have a variable number.