How to create all possible pairs from a list of text data

[sjSQW]

I have a list of postcodes from which I want to create two new columns that
contain all the possible pairs of postcodes. So, for example, if I have PC1,
PC2, PC3 as three cells in a single column, I want two new columns created
with the following:
PC1-PC1
PC1-PC2
PC1-PC3
PC2-PC1
PC2-PC2
PC3-PC3
PC3-PC1
PC3-PC2
PC3-PC3
(The dash (-) is supposed to indate the next column.)

Can anyone tell me if this is possible and if so how to do it?

Plus, if possible, there is one further complication that I would like to be
able to do. As well as the list of postcodes, for each postcode I know which
general area it is in (i.e. in a second column). What I would ideally like to
do is for Excel to create the possible pairs of postcodes (as above) in the
same area, and then move on to generate all the possible pairs for the next
area and then the next area and so on. I hope that makes sense.

Is this possible? And if so how?

 

[Bernard Liengme]

From my e-notebook:
Permutations
Myrna Larson's power subroutine ..

Take away this implemented sample from my archives:
http://www.savefile.com/files/518493
MyrnaLarson_Combination_Permutation.xls


best wishes

 

[sjSQW]

Dear Bernard,

Thanks for taking the time to respond. Unfortunately your spreadsheet
doesn't solve the problem for me. Your spreadsheet creates all permutations
and combinations, it does not create all possible pairs of values. Do you
have any further thoughts by any chance?

Thanks in advance,

Stuart