Combine all possible combinations from single column in msaccess

[Sriram Iyengar]

Hi Team,

I have 9148 rows of city codes in an excel file
I need to concatenate these values e.g. below

1) BOM
2) CCU
3) LHR
4) CDG

Output needs to be as

BOMCCU
BOMLHR
BOM CDG
CCUBOM
CCULHR

and so on
CCUCDG

I cant do this in excel since excel will only allow 1,00,000 rows & for every single code there will be 9143 pairings.

 

[Hui]

Try this

It will start making a combined column in Column C and once it gets past 1,000,000 rows will continue at Column D etc

Sub Make_List()

Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row

Dim separator As String
separator = ";"

Dim myrow As Long, mycol As Integer
myrow = 1: mycol = 3

Dim i As Long
For i = 1 To lr - 1
   For j = i + 1 To lr
      If myrow > 1000000 Then
         myrow = 1
         mycol = mycol + 1
      End If
      Cells(myrow, mycol) = Cells(i, 1).Text & separator & Cells(j, 1).Text
      myrow = myrow + 1
   Next j
Next i

End Sub

 

[Stevie]

Hi Sriram,
Hui's code will produce the results you are looking for, but it may be worth considering the requirements at a higher level. What will you be using these pairings for?
I ask this as 9148 C 2 = 41838378 (9148 P 2 = 83767756 if order matters! - it seems to in your example), which is a large amount of cells to check for a lookup of some kind. Perhaps there is a way you can generate portions of this set on the fly, or store the data in another way for efficiency's sake?

That being said I am just adding my two cents as Hui's response answers your question already.

Stevie ^.^

 

[Chihiro]

If doing it in MS Access. You can follow process below for Crossjoin.

1. Copy table1 and paste as new table (table2)
2. Create -> Query
3. Add table1 & 2 to query.
4. Select desired column from both.
5. Select "Make Table" in query type.
5. Go into SQL view. Edit CrossJoin query, to concatenate columns instead of selecting both columns.
Ex:

SELECT table1.Column + table2.Column as JoinedColumn
INTO NewTable
FROM table1, table2;

6. Drop table2 or keep it as needed.

EDIT: FYI you can use self join instead, if you don't want to copy table1.
Ex:

SELECT table1.Column + t2.Column as JoinedColumn
INTO NewTable
FROM table1, table1 AS t2;