Quote:
Originally Posted by desertanimal
I can get as far as getting excel to recognize matches by concatenating the information from two columns(let's say in a new column C), then reversing the text string of the concatenation (let's say in a new column D), and telling column E to =IF(MATCH(C8,$D$8:$D$1996,0),1,0).
But all this does is return a 1 in column E for matches 1-3, 3-1; 2-6, 6-2; 12-13, 13-12, etc. What I really want to be able to do is get it to return a unique result for each unique pair, recognizing that 1-3 is not different from 3-1 but IS different from 1-4 and 4-1, which, in turn, are not different from each other.
Any ideas?
|
What you can do is that: Let Column A label with numbers 1 - 13 until row 169, then for Column B label with letters A - M until row 169. Meaning;
A B
1 A
2 A
3 A
4 A
5 A
6 A
This way, you won't have any issue with duplicates (ie. 1-13 with 11-3 or 11-2 with 1-12).
Next columns will be the possible combination. Column C will have CONCATENATE(A1,B1), column D will have CONCATENATE(B1,A1).
For column E will be your PAIR labeled as =CONCATENATE("Pair ",A1," - ",B1).
Let's say the query is at G1. On H1, your formula should be:
=IF(ISERROR(VLOOKUP(G1,$C$1:$E$182,3,0)),VLOOKUP(G 1,$D$1:$E$182,2,0),VLOOKUP(G1,$C$1:$E$182,3,0))