Excel - list in two columns each unique-pair from two source columns in excel

Question

I've been using index, match and countif to list unique values happily for some time, but now I want to list unique pairs from an excel sheet based on two columns of values. For example, if the data is so:

Input

> Col. 1   Col. 2
  A        x   
  A        x
  B        x 
  B        y
  B        x
  C        x 
  C        y 
  C        x

The unique list of pairs should be:

> Col. 1   Col. 2
  A        x   
  B        x 
  B        y
  C        x 
  C        y

This seems to be a common suggestion: =INDEX(!D$4:D$5, MATCH(0, COUNTIF($A$57:$A59,!$C$4:$C$5) * COUNTIF($B$57:$B59, !$D$4:$D$5), 0)),"") But will not return a value like the pair C and y, in the example, because C and y have appeared already - in different pairs.

I've tried concat (doesn't work inside countif), sumproduct (doesn't seem to like sumproduct(--($A$57:$A59=!$C$4:$C$5), --($B$57:$B59 = !$D$4:$D$5)); and countifs($A$57:$A59,!$C$4:$C$5),$B$57:$B59,!$D$4:$D$5) (returns error).

PS - I know nothing of VB, and for various reasons need a formula approach.

Tom Sharpe Tom Sharpe · Accepted Answer · 2018-11-06T18:53:54

You could use COUNTIFS:

=IFERROR(INDEX($A$2:$B$9,MATCH(0,COUNTIFS($D$1:$D1,$A$2:$A$9,$E$1:$E1,$B$2:$B$9),0),1),"")

for the first column and

=IFERROR(INDEX($A$2:$B$9,MATCH(0,COUNTIFS($D$1:$D1,$A$2:$A$9,$E$1:$E1,$B$2:$B$9),0),2),"")

for the second column

entered as an array formula using CtrlShiftEnter

Note the formulas will give a spurious extra row if blank cells are included - this can be fixed:

=IFERROR(INDEX($A$2:$B$9,MATCH(1,(COUNTIFS($G$1:$G1,$A$2:$A$9,$H$1:$H1,$B$2:$B$9)=0)*($A$2:$A$9<>"")*($B$2:$B$9<>""),0),1),"")

=IFERROR(INDEX($A$2:$B$9,MATCH(1,(COUNTIFS($G$1:$G1,$A$2:$A$9,$H$1:$H1,$B$2:$B$9)=0)*($A$2:$A$9<>"")*($B$2:$B$9<>""),0),2),"")

Excel - list in two columns each unique-pair from two source columns in excel

2 Answers