Combination of numbers in a range

[audax48]

Please help me to find a solution...I have populated column C and Column D with single numbers and Column E the numbers are concatenated. How do I generate a list of all possible COMBINATIONS of the Column E with all the other single numbers located in Columns C and D except numbers located in the same Row of the concatenated numbers? For Ex.C D E
2 9 29
0 7 07
3 8 38


Column F (Output)
290
297
293
298
072
079
073
078
382
389
380
387
NOTE:29 was NOT combined with 2 and 9 (same Row)...same for 07 NO combined with 0 and 7
and 38 NO combined with 3 and 8. Than you in advanced

 

[Takae]

Hope this helps.

Sub test()
Dim r As Long, j As Long, i As Long, k As Long
Dim rng As Range
Dim c
Set rng = Range(Range("E2"), cells(Rows.count, 5).End(xlUp))

j = 2
For Each c In rng
    For k = 3 To 4
        For i = 2 To cells(Rows.count, 3).End(xlUp).Row
            If i <> c.Row Then
                cells(j, 6).Value = "'" & CStr(c.Value) & CStr(cells(i, k).Value)
                j = j + 1
            End If
        Next
    Next
Next
End Sub

 

[audax48]

Thank you Mr Takae trying to help me....almost 100%...it's possible to change End(xlUp) because under my numbers I have formulas and start to give me other combinations...maybe changing endxlup to last row with numbers?

Hope this helps.

Sub test()
Dim r As Long, j As Long, i As Long, k As Long
Dim rng As Range
Dim c
Set rng = Range(Range("E2"), cells(Rows.count, 5).End(xlUp))

j = 2
For Each c In rng
    For k = 3 To 4
        For i = 2 To cells(Rows.count, 3).End(xlUp).Row
            If i <> c.Row Then
                cells(j, 6).Value = "'" & CStr(c.Value) & CStr(cells(i, k).Value)
                j = j + 1
            End If
        Next
    Next
Next
End Sub

 

[Takae]

You can write like this.

Sub test()
Dim r As Long, j As Long, i As Long, k As Long
Dim rng As Range
Dim c
Set rng = Range(Range("E2"), Range("E10"))

j = 2
For Each c In rng
    For k = 3 To 4
        For i = 2 To 10
            If i <> c.Row Then
                cells(j, 6).Value = "'" & CStr(c.Value) & CStr(cells(i, k).Value)
                j = j + 1
            End If
        Next
    Next
Next
End Sub

 

[audax48]

Thank you again Mr Takae.....this last one work nice if you know E10 is your last cell with numbers, but the list with numbers is variable, the last number may be in E25 or any other row of the column E, how we can change the last range to point to the last number in Column E?

You can write like this.

Sub test()
Dim r As Long, j As Long, i As Long, k As Long
Dim rng As Range
Dim c
Set rng = Range(Range("E2"), Range("E10"))

j = 2
For Each c In rng
    For k = 3 To 4
        For i = 2 To 10
            If i <> c.Row Then
                cells(j, 6).Value = "'" & CStr(c.Value) & CStr(cells(i, k).Value)
                j = j + 1
            End If
        Next
    Next
Next
End Sub