Condense data by removing blank rows (without deleting)

[zjamespryor]

Hello,

I am using Google Sheets for this project. I am wondering if there is a way to condense data without deleting rows. In my example below I have the dataset being collected by a Google Form. In cell A9 I have a formula to grab values out of the data set and transpose them. I am wondering if there is an easy way to condense the data that is being collected there so I can reference it elsewhere more easily. If it can't be done in the transposed form, can it be done in the INDEX function to the right? Thank you for any help you can offer. The ultimate solution I am working towards is to be able to lookup the person's name and show the sports in the dataset that have a value.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Dataset
2	School	Name	Track	Wrestling	Golf	Baseball		Lookup:	James
3	Virginia	James	Gold		Silver	Bronze			Sports	Desired Result:
4	California	Dan		Bronze	Gold	Silver			Track	Track
5	Nebraska	Bill	Silver	Gold	Bronze					Golf
6	Florida	Frank	Bronze	Silver		Gold			Golf	Baseball
7									Baseball
8	James	Dan	Bill	Frank
9	Track		Track	Track
10		Wrestling	Wrestling	Wrestling
11	Golf	Golf	Golf
12	Baseball	Baseball		Baseball
13
14	Desired Result:
15	James	Dan	Bill	Frank
16	Track	Wrestling	Track	Track
17	Golf	Golf	Wrestling	Wrestling
18	Baseball	Baseball	Golf	Baseball
19
Sheet1

Cell Formulas
Range	Formula
I4:I7	I4	=INDEX(A9:D12,,MATCH(I2,A8:D8,0))
A8:D8,A15:D15	A8	=TRANSPOSE($B$3:$B$6)
A9:A12	A9	=TRANSPOSE(IF($C3:$F3 <> "", $C$2:$F$2, ""))
B9:B12	B9	=TRANSPOSE(IF($C4:$F4 <> "", $C$2:$F$2, ""))
C9:C12	C9	=TRANSPOSE(IF($C5:$F5 <> "", $C$2:$F$2, ""))
D9:D12	D9	=TRANSPOSE(IF($C6:$F6 <> "", $C$2:$F$2, ""))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
I2	List	=$B$3:$B$6

 

[zjamespryor]

Someone helped me shortly after I posted this with a more concise way to get the filtered results. That post is here. If someone is still interested in showing how to stack the data together and remove the rows, I am still interested in learning how to do that. Thanks!