Values from different columns lined up in one column, in pivot table?

First I'd use a series of VLOOKUP, COUNTIF, MATCH, and INDEX to reorganize the data into 4 columns for easier manipulation first. (doing it by hand would be a pain)

Assuming your example data puts Tony in A9, in A10 I go with =IF(COUNTIF($A$2:A9,A2)<COUNTIF($C$1:$Z$1,"Age"),A2,"") and copy downwards.

This will repeat the names for as many times as another age occurs.

B10: =IFERROR(VLOOKUP(A10,$A$2:$B$9,2,0),""), and matches the Gender to the name.

C10: =IFERROR(INDEX($C$2:$H$9,MATCH($A10,$A$2:$A$9,0),(COUNTIF($A$2:$A10,$A10)*2)-2+COLUMN(A1)),"")

D10: =IFERROR(INDEX($C$2:$H$9,MATCH($A10,$A$2:$A$9,0),(COUNTIF($A$2:$A10,$A10)*2)-2+COLUMN(B1)),"")

I copy A10:D10 down as far as needed.

The somewhere off to the side, in AA2, I rank the ages.

AA2: =IF(OR(B2="M",B2="F"),COUNTIF($B$2:$B$50,B2)-((SUMPRODUCT(($B$2:$B$50=B2)*($C$2:$C$50<C2))+1)+COUNTIFS($C$2:C2,C2,$B$2:$B2,B2)-1)+1,"")

Entered as array using Ctrl+Shift+Enter to confirm, instead of normal Enter.




Lastly, we pull the data with index and match, using the Ranks as markers.

On the second tab, I put Men in A1, Age in A2, Weight in A3

B1: =COUNTIF(Sheet1!$B$2:$B$500,"M")

C1: =IFERROR(IF(B1-1<1,"",B1-1),""), and then copy it right a bunch

B2: =INDEX(Sheet1!$C$2:$D$25,MATCH(1,(B$1=Sheet1!$AA$2:$AA$25)*("M"=Sheet1!$B$2:$B$25),0),ROW(A1))

This is another array formula.

B3: drag formula from B2 into B3, copy as far over as needed.



For women, repeat as above, changing M to F and changing =INDEX(Sheet1!$C$2:$D$25,MATCH(1,(B$1[=Sheet1!$AA$2:$AA$25)*([b]"F"[b]=Sheet1!$B$2:$B$25),0),ROW(A1)) to match whatever row you put the women's ranks in.

data fun.xlsx