Copying column of data while removing spaces

[Hwatsonnn]

I have a column of values on Sheet 1 (A:A). There are blanks in this data and these are dependant on the value in column B.
The blanks vary from week to week.

On Sheet 2 I would like the data carried over from Sheet 1 (A:A) but grouped up without spaces, I can’t just paste and remove blanks as the data is going to be varying all the time.

Thanks for any help

 

[braindiesel]

Here is a solution with a formula

Book4
	A	B	C	D	E
1
2	Data1				Data1
3	Data2				Data2
4	Data3				Data3
5	Data4				Data4
6					Data6
7	Data6				Data7
8	Data7				Data9
9					Data10
10	Data9				Data12
11	Data10				Data13
12					Data15
13	Data12
14	Data13
15
16	Data15
17
18
19
20
Sheet1

Cell Formulas
Range	Formula
E2:E20	E2	=IF(ROWS($E$2:E2)<=COUNTA($A$1:$A$20),INDEX($A$1:$A$20,SMALL(($A$1:$A$20<>"")*ROW($A$1:$A$20),ROW(A1)+COUNTIF($A$1:$A$20,"")),1),"")

 

[Hwatsonnn]

Here is a solution with a formula

Book4
	A	B	C	D	E
1
2	Data1				Data1
3	Data2				Data2
4	Data3				Data3
5	Data4				Data4
6					Data6
7	Data6				Data7
8	Data7				Data9
9					Data10
10	Data9				Data12
11	Data10				Data13
12					Data15
13	Data12
14	Data13
15
16	Data15
17
18
19
20
Sheet1

Cell Formulas
Range	Formula
E2:E20	E2	=IF(ROWS($E$2:E2)<=COUNTA($A$1:$A$20),INDEX($A$1:$A$20,SMALL(($A$1:$A$20<>"")*ROW($A$1:$A$20),ROW(A1)+COUNTIF($A$1:$A$20,"")),1),"")

Thanks a lot, works great

 

[braindiesel]

Glad to hear it. Thanks for letting me know