|
Not entirely sure I'm following but if I've interpreted correctly I'd probably go for something along the lines of the following:
1 - Sheet Resultaat:
B2: =IF(AND(vrouwen!B2="x",mannen!B2="x"),COUNT($A2:A2),"")
copy down for all rows and across to column AY
AZ2:=COUNT(B2:AY2)
copy down for all rows (effectively = count of *matches* for given man)
2 - Sheet Resultaat mannen
Insert Row in Row 1 (ie existing row 1 becomes row 2)
In D1: "Match"
In B2: "Row"
In C2: "Matches"
In D2: =N(C2)+1
copy across to say J2
In B3: =MATCH($A3,Resultaat!$A$1:$A$100,0)
copy down for all Man ID's in A
In C3: =INDEX(Resultaat!$AZ$1:$AZ$100,$B3,1)
copy down for all Man ID's in A
Now to populate matrix
In D3: =IF($C3<D$2,"",INDEX(Resultaat!$A$1:$AZ$1,1,MATCH(D$2,INDEX(Resultaat!$A$1:$AZ$100,$B3,0),0)))
copy down for all Man ID's in A and across to J (ie to cater for max possible number of matches)
Hope that helps.
(note 2nd use of INDEX in the Matrix is volatile as it's being used to return a Range as opposed to a Value)
|
Linear Mode
