Combining 2 lists (for each cell in first list assign all cells in second list)

[Oredo]

Hello all,

Would like some help with an issue and i hope i won't be much of the trouble. I would like to combine 2 tables into one with 2 columns, where each cell in table 1 would get all cell values from table 2.
[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
table1
[TABLE="width: 50"]
<tbody>[TR]
[TD]austin[/TD]
[/TR]
[TR]
[TD]justin[/TD]
[/TR]
[TR]
[TD]paul[/TD]
[/TR]
[TR]
[TD]mary[/TD]
[/TR]
</tbody>[/TABLE]

table2
[TABLE="width: 50"]
<tbody>[TR]
[TD]apple[/TD]
[/TR]
[TR]
[TD]banana[/TD]
[/TR]
[TR]
[TD]orange[/TD]
[/TR]
</tbody>[/TABLE]

result table:
[TABLE="width: 100"]
<tbody>[TR]
[TD]austin[/TD]
[TD]apple[/TD]
[/TR]
[TR]
[TD]austin[/TD]
[TD]banana[/TD]
[/TR]
[TR]
[TD]austin[/TD]
[TD]orange[/TD]
[/TR]
[TR]
[TD]justin[/TD]
[TD]apple[/TD]
[/TR]
[TR]
[TD]justin[/TD]
[TD]banana[/TD]
[/TR]
</tbody>[/TABLE]

I appreciate you your time and help everyone.

 

[mohadin]

HI
Table1 in A2:austen...
Table2 in b2:apple...
Then try:

Sub whatthe()
    list1 = Application.Transpose(Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row))
    list2 = Application.Transpose(Range("b2:b" & Cells(Rows.Count, 2).End(xlUp).Row))
    ReDim result(1 To UBound(list1) * UBound(list2), 1 To 2)
    k = 1: l = 1
    For i = 1 To UBound(list1)
        For j = 1 To UBound(list2)
            For k = 1 To 1
                result(l, k) = list1(i)
                result(l, k + 1) = list2(j)
            Next
            l = l + 1
        Next
    Next
    Range("c1").Resize(UBound(result, 1), UBound(result, 2)) = result
End Sub

 

[sandy666]

or with PowerQuery aka Get&Transform

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]table1[/td][td][/td][td=bgcolor:#5B9BD5]table2[/td][td][/td][td=bgcolor:#70AD47]table1[/td][td=bgcolor:#70AD47]table2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]austin[/td][td][/td][td=bgcolor:#DDEBF7]apple[/td][td][/td][td=bgcolor:#E2EFDA]austin[/td][td=bgcolor:#E2EFDA]apple[/td][/tr]

[tr=bgcolor:#FFFFFF][td]justin[/td][td][/td][td]banana[/td][td][/td][td]austin[/td][td]banana[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]paul[/td][td][/td][td=bgcolor:#DDEBF7]orange[/td][td][/td][td=bgcolor:#E2EFDA]austin[/td][td=bgcolor:#E2EFDA]orange[/td][/tr]

[tr=bgcolor:#FFFFFF][td]mary[/td][td][/td][td][/td][td][/td][td]justin[/td][td]apple[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]justin[/td][td=bgcolor:#E2EFDA]banana[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]justin[/td][td]orange[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]paul[/td][td=bgcolor:#E2EFDA]apple[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]paul[/td][td]banana[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]paul[/td][td=bgcolor:#E2EFDA]orange[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]mary[/td][td]apple[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]mary[/td][td=bgcolor:#E2EFDA]banana[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td]mary[/td][td]orange[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table2),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"table2"}, {"table2"})
in
    #"Expanded Custom"[/SIZE]

 

[Oredo]

Thank you both for resolve. i'm sure will use both solutions.

 

[sandy666]

you are welcome

have a nice day

 

[Rick Rothstein]

For those who might be interested, here is a macro that does what the OP asked for without using a loop...

[table="width: 500"]
[tr]
	[td]Sub NamesAndFruits()
  Dim List1 As Variant, List2 As Variant, Nme As String, Fruit As String
  List1 = Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp)))
  List2 = Application.Transpose(Range("B1", Cells(Rows.Count, "B").End(xlUp)))
  Range("E1").Resize(UBound(List1) * UBound(List2)) = Application.Transpose(Split(Application.Rept(Join(List2, Chr(1)) & Chr(1), UBound(List1)), Chr(1)))
  With Range("D1").Resize(UBound(List1) * UBound(List2))
    .Value = Application.Transpose(Split(Join(List1, Application.Rept(Chr(1), UBound(List2))) & Application.Rept(Chr(1), UBound(List2)), Chr(1)))
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub[/td]
[/tr]
[/table]

 

[mohadin]

You are very well come
Be happy