=Sheet!A1 formula alternative

  • Thread starter Thread starter RLD
  • Start date Start date
R

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?
 
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.
 
Back
Top