Vlookup to move names from one worksheet to another

[PondWaters]

I have a list of names, and sports that they play - noted by X's. I am ok with moving all of the data over from one worksheet to another using an if & vlookup. However, i am running into an issue with the formatting. Whenever I move the data over, there is always a blank space between names that are yes (x) and no. Is there a way for me to remove the spaces, or do I need to change my formula? Any assistance would be appreciated.

This is the main page

Master Sheet

*	A	B	C	D	E
Name	Basketball	Baseball	Football	Rugby
John	x	*	*	*
James	x	x	*	*
Joanna	x	x	x	x
Phil	*	x	*	x
Joseph	x	*	*	*
George	x	x	x	*
Doug	*	x	*	x

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


This is the set of results - what I want to show is only those who have x's - I know this is easy, but for some reason I drew a complete blank today.

Football

*	A
Name
*
*
Joanna
*
*
George
*
*
*
*
*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A2 =IF('Master Sheet'!D:D="x",VLOOKUP('Master Sheet'!A2,'Master Sheet'!A:E,1,0),"") A3 =IF('Master Sheet'!D:D="x",VLOOKUP('Master Sheet'!A3,'Master Sheet'!A:E,1,0),"") A4 =IF('Master Sheet'!D:D="x",VLOOKUP('Master Sheet'!A4,'Master Sheet'!A:E,1,0),"") A5 =IF('Master Sheet'!D:D="x",VLOOKUP('Master Sheet'!A5,'Master Sheet'!A:E,1,0),"") A6 =IF('Master Sheet'!D:D="x",VLOOKUP('Master Sheet'!A6,'Master Sheet'!A:E,1,0),"") A7 =IF('Master Sheet'!D:D="x",VLOOKUP('Master Sheet'!A7,'Master Sheet'!A:E,1,0),"") A8 =IF('Master Sheet'!D:D="x",VLOOKUP('Master Sheet'!A8,'Master Sheet'!A:E,1,0),"") A9 =IF('Master Sheet'!D:D="x",VLOOKUP('Master Sheet'!A9,'Master Sheet'!A:E,1,0),"") A10 =IF('Master Sheet'!D:D="x",VLOOKUP('Master Sheet'!A10,'Master Sheet'!A:E,1,0),"") A11 =IF('Master Sheet'!D:D="x",VLOOKUP('Master Sheet'!A11,'Master Sheet'!A:E,1,0),"") A12 =IF('Master Sheet'!D:D="x",VLOOKUP('Master Sheet'!A12,'Master Sheet'!A:E,1,0),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Mytå]

Hi Forum

With an Array Formula

=SI(LIGNES($1:1)<=NB.SI($D$1:$D$8;"X");INDEX($A$1:$A$8;PETITE.VALEUR(SI($D$1:$D$8="X";LIGNE(INDIRECT("1:"&LIGNES($D$1:$D$8))));LIGNES($1:1)));"")

Valid by Ctrl + Shift + Enter and copy drop down

File : Liste.xls

Mytå

 

[PondWaters]

Would it be better to use a pivot table and get pivot data? Or easier?