generating difference between unique combinations of datasets

[digger1]

Hi,

I was wondering if someone would be able to help me.
I have data in two columns, sites in column A, and the slope for each site in column B

eg:

SITE slope
1 2
2 -2
3 4

(and so on for 20 sites)

I want to find all unique 2 site combinations of the sites in column A, without a site being compared with itself, and without repeats (ie, 1,2 would be a valid pairing, but 1,1 would not, and 2,1 would not as it repeats the same pairing as 1,2), and then have these combinations output as a list into a third column (say column D) in the format 1,2 (showing in this case a pairing of site 1 and 2).

Furthermore, I then want a fourth column to be generated which gives the difference between the slopes which are associated with the sites in each pair, but ensuring this is a positive value (ie for the pairing of site 1 and site 2 described above, the slope difference is 4, and this would be output in the column E alongside the column D entry that tells me which sites this difference value relates to, in this case 1,2).

Does anybody know how I would go about doing this?

Thanks,

Paul

[MrShorty]

Divide the problem into smaller problems, then solve the smaller problems.

1st, and probably the most difficult problem, is generating the permutations. Using lexicographic order, we can start with 1,2 then 1,3 and so on to 19,20. As I loathe the idea of putting two pieces of information into one cell, I would not combine the pairings into one cell as you've requested. It always seems that, once I've combined information like that, then subsequent formulas need some way to re-separate the information. In a spreadsheet, this could look something like this:

1) D2=1, E2=2 to initialize the algorithm
2) D3=IF(e2<20,d2,d2+1) E3=IF(e2<20,e2+1,d3+1) copied down until all permutations are generated.

Once the permutations are generated, you can use the INDEX() function to return the individual slopes [F2=INDEX($B$2:$B$21,D2) copied to G and down to end].
Difference is of course a simple subtraction, then use the absolute value function to force it to be positive [H2=ABS(f2-g2)]

[tigeravatar]

digger1,

Welcome to the forum!
I found this thread and the challenge presented within it interesting, so I gave it a shot.

Attached is an example workbook based on the criteria you described.
It uses dynamic named ranges and a helper column to get the correct permutations depending on the number of sites listed in column A. As MrShorty has already suggested, I also prefer to have information listed in separate cells rather than combined into a single cell.

The first named range is named list_Sites and it is defined with this formula:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MAX(2,ROWS(Sheet1!$A:$A)-COUNTBLANK(Sheet1!$A:$A)))

The second named range is named list_Slopes and it is defined with this formula:

=OFFSET(list_Sites,,1)

Next I used column M as a helper column. I have hidden the column, and you can unhide it if you'd like to see it. In cell M1 is the number 0. This is used to start the helper column. In cell M2 and copied down to cell M15 is this formula (I only copied to cell M15 to keep file size low. This allows you to have up to 15 sites and the permutations will auto-generate properly. To have more, copy down to a row number that is likely more than the maximum number of sites you'll have):

=IF(ROWS(M$1:M1)+1>ROWS(list_Sites),"",SUMPRODUCT(LARGE(INDEX(ROWS(list_Sites)-ROW(INDIRECT("1:"&ROWS(list_Sites)-1)),),ROW(INDIRECT("1:"&ROWS(M$1:M1))))))

So, with all that setup, we can create the formula to auto-generate the correct 2-site permutations based on the number of sites listed in column A. So, in cell D2 is this formula:

=IF(ROWS(D$1:D1)>COMBIN(ROWS(list_Sites),2),"",INDEX(list_Sites,MATCH(ROWS(D$1:D1)-1,$M$1:$M$15)))

In cell E2 is this formula:

=IF(D2="","",INDEX(list_Sites,MATCH(D2,list_Sites,0)+COUNTIF(D$2:D2,D2)))

Those are both copied down to row 106. The reason for row 106 is because from row 2 to 106 is 105 rows. 105 is the number of permutations if you had 15 sites. If you are going to have more sites, copy the formulas down further to accomodate the maximum number of permutations depening on the maximum number of sites.

Lastly, in cell F2 and copied down is this formula to get the absolute value of the slope difference:

=IF(D2="","",ABS(INDEX(list_Slopes,MATCH(D2,list_Sites,0))-INDEX(list_Slopes,MATCH(E2,list_Sites,0))))

[digger1]

Thank you both very much for your help, and for making a new user feel very welcome!

Out of interest I tried both your solutions, and they both worked perfectly and the answers agreed, so thanks once again!

All the best,

Paul