Counting unique values and it's reciprocal as one?

[sidt87]

Hi, I'm not sure if this is possible but I am trying to count a combination of two cell values (Concatenate function) and if there are any reciprocals as one. Example:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Player 1[/TD]
[TD]Player 2[/TD]
[TD]Combined Player[/TD]
[TD]Count[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bob[/TD]
[TD]Joe[/TD]
[TD]Bob&Joe[/TD]
[TD]{=IFERROR(INDEX($C$2:$C$5,MATCH(0,COUNTIF($D$1:D2,$C$2:$C$5&"")+ IF($C$2:$C$5="",1,0), 0)), "---")}[/TD]
[TD]Results = Bob&Joe[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Joe[/TD]
[TD]Bob[/TD]
[TD]Joe&Bob[/TD]
[TD]{=IFERROR(INDEX($C$2:$C$5,MATCH(0,COUNTIF($D$1:D3,$C$2:$C$5&"")+ IF($C$2:$C$5="",1,0), 0)), "---")}[/TD]
[TD]Results = Joe&Bob[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Bob[/TD]
[TD]Joe[/TD]
[TD]Bob&Joe[/TD]
[TD]{=IFERROR(INDEX($C$2:$C$5,MATCH(0,COUNTIF($D$1:D4,$C$2:$C$5&"")+ IF($C$2:$C$5="",1,0), 0)), "---")}[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Joe[/TD]
[TD]Bob[/TD]
[TD]Joe&Bob[/TD]
[TD]{=IFERROR(INDEX($C$2:$C$5,MATCH(0,COUNTIF($D$1:D5,$C$2:$C$5&"")+ IF($C$2:$C$5="",1,0), 0)), "---")}[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I'd like to count Joe&Bob and Bob&Joe as 1 unique value because it is the same two person. Any thoughts on how to do this? Thanks!

 

[Eric W]

You didn't show your formula in C3, but I assume it's something like:

=A3&"&"&B3

I'd recommend changing it to:

<b3,a3&"&"&b3,b3&"&"&a3)
=IF(A3< B3,A3&"&"&B3,B3&"&"&A3)

This alphabetizes the names, so you'll only get one version to consider.</b3,a3&"&"&b3,b3&"&"&a3)

 

[sidt87]

Thanks Eric! Didn't know I could do that but yes that makes it so much easier. Much appreciated!

 

[Eric W]

Glad to help!