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:

	A	B	C	D
1	Player 1	Player 2	Combined Player	Count
2
3	Bob	Joe	Bob&Joe	{=IFERROR(INDEX($C$2:$C$5,MATCH(0,COUNTIF($D$1:D2,$C$2:$C$5&"")+ IF($C$2:$C$5="",1,0), 0)), "---")}	Results = Bob&Joe
4	Joe	Bob	Joe&Bob	{=IFERROR(INDEX($C$2:$C$5,MATCH(0,COUNTIF($D$1:D3,$C$2:$C$5&"")+ IF($C$2:$C$5="",1,0), 0)), "---")}	Results = Joe&Bob
5	Bob	Joe	Bob&Joe	{=IFERROR(INDEX($C$2:$C$5,MATCH(0,COUNTIF($D$1:D4,$C$2:$C$5&"")+ IF($C$2:$C$5="",1,0), 0)), "---")}
6	Joe	Bob	Joe&Bob	{=IFERROR(INDEX($C$2:$C$5,MATCH(0,COUNTIF($D$1:D5,$C$2:$C$5&"")+ IF($C$2:$C$5="",1,0), 0)), "---")}

<tbody>
</tbody>

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!