Try on 'Pairings' sheet

In A2 formula below

Code:

=SUMPRODUCT((Data!$A$2:$A$7=C2)*(Data!$B$2:$O$7=VLOOKUP(D2;Codes!$C$2:$G$29;3;FALSE)))+SUMPRODUCT((Data!$A$2:$A$7=C2)*(Data!$B$2:$O$7=VLOOKUP(D2;Codes!$C$2:$G$29;4;FALSE)))+SUMPRODUCT((Data!$A$2:$A$7=C2)*(Data!$B$2:$O$7=VLOOKUP(D2;Codes!$C$2:$G$29;5;FALSE)))

In C2 Array formula formula below

Code:

=IF(ROWS(Data!A$2:A2)>Codes!$D$1*COUNTIF(Data!$A$2:$A$100;"="&"?*");"";INDEX(Data!$A$2:$A$100;SMALL(IF(Data!$A$2:$A$100<>"";ROW(Data!$A$2:$A$100)-ROW(Data!$A$2)+1);MOD(ROWS(Data!A$2:A2)-1;COUNTIF(Data!$A$2:$A$100;"="&"?*"))+1)))

In D2 formula below

Code:

=IFERROR(INDEX(Codes!$C$2:$C$29;INT((ROW(Data!A1)+4)/COUNTIF(Data!$A$1:$A$100;"*?")));"")

I do not understand the logic of calculation in column B (%)?

Try on 'Codes' sheet

There are 'helper' columns on 'Codes' worksheet

In C2 Array formula formula below

Code:

=IFERROR(INDEX($B$2:$B$29;MATCH(0;INDEX(COUNTIF($B$2:$B$29;"<"&$B$2:$B$29)-SUMPRODUCT(COUNTIF($B$2:$B$29;C$1:C1)););0));"")

In D1 formula below

Code:

=COUNTA(C2:C29)-COUNTBLANK(C2:C29)

In D2 Array formula formula below (copy across)

Code:

=IFERROR(INDEX($A$2:$A$29;MATCH(0;COUNTIF($C2:C2;$A$2:$A$29)+IF($B$2:$B$29<>$C2;1;0);0));"")

## Bookmarks