How to create a table of multiple combination

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.
 
J

James Ravenswood

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
 
A

Auric__

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top