=Sheet!A1 formula alternative

[RLD]

I have 2 sheets.
Sheet 1 contains text data from A1:A200 but not in consecutive order (for
example):
Sheet1
A
1 textone
2 texttwo
3
4 textmisc
5
6 textother

I need Sheet2!B1:B50 to grab all the data from Sheet1!A1:A200 and list them
in the order that they were entered in Sheet1 as shown below:

Sheet1 Sheet 2
A B
1 textone 1 textone
2 texttwo 2 texttwo
3 3 textmisc
4 textmisc 4 textother
5 5
6 textother

I tried a =Sheet1!A1 formula in Sheet 2 but I need to get rid of the blank
spaces in Sheet1!A3,A5. How can I do this?

 

[T. Valko]

Enter this array formula** in Sheet2B1 and copy down until you get blanks:

=IF(ROWS(B$1:B1)>COUNTA(Sheet1!A$1:A$200),"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!A$1:A200<>"",ROW(Sheet1!A$1:A$200)),ROWS(B$1:B1))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

Ooops!

Typo

SMALL(IF(Sheet1!A$1:A200

Should be:

SMALL(IF(Sheet1!A$1:A$200