Getting only the rows of data that have info on them to another sh
 

The information below is Part number, Quantity, and Days. These are just 3
columns from my sheet.
The problem is that I want to take these three peices of information to
another sheet and not have empty rows between them. If possible I would like
to have this automatice since the rest of my workbook is.

Any ideas?

SCE13000 2 2
SCE13000 2 2











SCE13001 30 5

SCE13006 10 1

Alex,

Assuming your sheet with the data is named "Data Sheet", and you have no
more than 1000 rows of data, enter this array formula (enter with
Ctrl-Shift-Enter) into cell A1 of your other sheet:

=IF(INDEX('Data Sheet'!A:A,SMALL(IF(ISTEXT('Data
Sheet'!$A$1:$A$1000),ROW('Data
Sheet'!$A$1:$A$1000),10000),ROW()),1)=0,"",INDEX(' Data
Sheet'!A:A,SMALL(IF(ISTEXT('Data Sheet'!$A$1:$A$1000),ROW('Data
Sheet'!$A$1:$A$1000),10000),ROW()),1))

Then copy to Cells B1:C1, and then copy A1:C1 down for as far as you need.

HTH,
Bernie
MS Excel MVP

"Alex" wrote in message
...
The information below is Part number, Quantity, and Days. These are just
3
columns from my sheet.
The problem is that I want to take these three peices of information to
another sheet and not have empty rows between them. If possible I would
like
to have this automatice since the rest of my workbook is.

Any ideas?

SCE13000 2 2
SCE13000 2 2











SCE13001 30 5

SCE13006 10 1

Hi!

Assume the data is in the range Sheet1 A1:C10.

You want to extract the data to Sheet2 A1:Cn.

In Sheet2 A1 enter this formula with the key combo of
CTRL,SHIFT,ENTER:

=INDEX(Sheet1!A$1:A$10,SMALL(IF(Sheet1!A$1:A$10<" ",ROW
(A$1:A$10)),ROW(1:1)))

Copy across to C1 then down until you get #NUM! errors
meaning the data has been exhausted.

Note: ROW(A$1:A$10) refers to the SIZE of the range and
not the physical location of that range. If the actual
physical location of the range was A2:A11, the size of the
range would still be 1:10. This confuses a lot of people!

If you want to trap the #NUM! errors:

=IF(ISERROR(ABOVE_FORMULA)),"",ABOVE_FORMULA))

Also entered as an array: CTRL,SHIFT,ENTER.

Biff

-----Original Message-----
The information below is Part number, Quantity, and
Days. These are just 3
columns from my sheet.
The problem is that I want to take these three peices of
information to
another sheet and not have empty rows between them. If
possible I would like
to have this automatice since the rest of my workbook is.

Any ideas?

SCE13000 2 2
SCE13000 2 2











SCE13001 30 5

SCE13006 10 1


.