How to create a table of multiple combination

  • Thread starter Thread starter amirstal
  • Start date Start date
A

amirstal

I'd appreciate your help with creating this table.

I have the following data in the following cells (for example - the
real table is much longer):
A1 USD
A2 EUR
A3 GBP
A4 JPY
A5 CHF

I'd like to create a list of all possible combination of 2 cells
combined, e.g., USDEUR, USDGBP, USDJPY, USDCHF, EURGBP, EURJPY,
EURCHF, GBPJPY, GBPCHF and JPYCHF. The new created table should avoid
repetition of a combination that was already created (USDEUR and
EURUSD for example). And the new table should be in a single column if
possible.

Thanks.
 
How about:

Sub Combine()
Dim I As Long, J As Long, N As Long, K As Long
N = Cells(Rows.Count, "A").End(xlUp).Row
K = 1
For I = 1 To N
v1 = Cells(I, 1)
For J = I + 1 To N
Cells(K, 2).Value = v1 & Cells(J, 1).Value
K = K + 1
Next
Next
End Sub

This will produce:

USDEUR
USDGBP
USDJPY
USDCHF
EURGBP
EURJPY
EURCHF
GBPJPY
GBPCHF
JPYCHF
 
James said:
How about:

Sub Combine()
Dim I As Long, J As Long, N As Long, K As Long
N = Cells(Rows.Count, "A").End(xlUp).Row
K = 1
For I = 1 To N
Click to expand...

This line needs to be changed to this:
For I = 1 To N - 1
 
Back
Top