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?

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(Sheet 1!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.

--
Biff
Microsoft Excel MVP


"RLD" wrote in message
...
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?

Ooops!

Typo

SMALL(IF(Sheet1!A$1:A200

Should be:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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(Sheet 1!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.

--
Biff
Microsoft Excel MVP


"RLD" wrote in message
...
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?