Copy a column leaving out any blanks
 

I have a column of names ie:

A
1 Bill
2 John
3
4 Mike
5 Rex
6 Stu

On another sheet, I want to have it list all the names on that list down a
column, but I want it to not carry over any blank cells. So on sheet2 it
would look like this:

A
1 Bill
2 John
3 Mike
4 Rex
5 Stu

Do you know how I can make that happen? Thank you in advance.

Copy a column leaving out any blanks
 

hi,
i would sort the names to force blanks to the bottom. Copy the names only to
sheet 2. I would then use the fill function to put the numbers next to each
name i.e. high light the column down to where you want the numbers.
EditFillSeriescolumnslinearstepvalue = 1ok.

Regards
FSt1


"Mike Pearson" wrote:

I have a column of names ie:

A
1 Bill
2 John
3
4 Mike
5 Rex
6 Stu

On another sheet, I want to have it list all the names on that list down a
column, but I want it to not carry over any blank cells. So on sheet2 it
would look like this:

A
1 Bill
2 John
3 Mike
4 Rex
5 Stu

Do you know how I can make that happen? Thank you in advance.

Copy a column leaving out any blanks
 

How about using Autofilter to help?

Select the range of names, beginning with the
top name (or column label, if you have one)

From the Excel main menu:
<data<filter<autofilter

Click the dropdown arrow at the top of the list
Select..... "(nonblanks)".....(Now, only the non-blank cells are visible)

Select the list of visible names....The hidden blanks will not be copied
<edit<copy

Switch to the new location
Press [Enter] to paste

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Mike Pearson" wrote:

I have a column of names ie:

A
1 Bill
2 John
3
4 Mike
5 Rex
6 Stu

On another sheet, I want to have it list all the names on that list down a
column, but I want it to not carry over any blank cells. So on sheet2 it
would look like this:

A
1 Bill
2 John
3 Mike
4 Rex
5 Stu

Do you know how I can make that happen? Thank you in advance.

Copy a column leaving out any blanks
 

=IF(ISERR(SMALL(IF(names<"",ROW(INDIRECT("1:"&ROW S(names)))),ROWS($1:1))),"",INDEX(names,SMALL(IF(n ames<"",ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1: 1))))

ctrl+shift+enter, not just enter
copy down


"Mike Pearson" wrote:

I have a column of names ie:

A
1 Bill
2 John
3
4 Mike
5 Rex
6 Stu

On another sheet, I want to have it list all the names on that list down a
column, but I want it to not carry over any blank cells. So on sheet2 it
would look like this:

A
1 Bill
2 John
3 Mike
4 Rex
5 Stu

Do you know how I can make that happen? Thank you in advance.

Copy a column leaving out any blanks
 

Since you DID post in the Worksheet.Functions group
......Maybe you'd like a formula? :)

On Sheet1, cells A1:A20 contain a list of names and some blank cells

On Sheet2
Put this ARRAY FORMULA (committed with [Ctrl]+[Shift]+[Enter], instead of
just [Enter]in .....

A1:
=IF(COUNTA(Sheet1!$A$1:$A$20)=ROWS($1:1),INDEX(Sh eet1!$A$1:$A$20,SMALL(IF(Sheet1!$A$1:$A$20<"",ROW (Sheet1!$A$1:$A$20)),ROWS($1:1))),"")

Copy A1 and paste into A2 and down as far as you need.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

How about using Autofilter to help?

Select the range of names, beginning with the
top name (or column label, if you have one)

From the Excel main menu:
<data<filter<autofilter

Click the dropdown arrow at the top of the list
Select..... "(nonblanks)".....(Now, only the non-blank cells are visible)

Select the list of visible names....The hidden blanks will not be copied
<edit<copy

Switch to the new location
Press [Enter] to paste

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Mike Pearson" wrote:

I have a column of names ie:

A
1 Bill
2 John
3
4 Mike
5 Rex
6 Stu

On another sheet, I want to have it list all the names on that list down a
column, but I want it to not carry over any blank cells. So on sheet2 it
would look like this:

A
1 Bill
2 John
3 Mike
4 Rex
5 Stu

Do you know how I can make that happen? Thank you in advance.

Copy a column leaving out any blanks
 

Another way to make it happen dynamically via simpler non-array formulae

Assume source data is in Sheet1, running in A1 down

In Sheet2,

Put in A1:
=IF(Sheet1!A1="","",ROW())

Put in B1:
=IF(ROW()COUNT(A:A),"",INDEX(Sheet1!A:A,SMALL(A:A ,ROW())))
Select A1:B1, copy down to the max expected extent of data in Sheet1's col
A, say copy down to B200. Hide away col A. Col B will return the required
results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mike Pearson" wrote:
I have a column of names ie:

A
1 Bill
2 John
3
4 Mike
5 Rex
6 Stu

On another sheet, I want to have it list all the names on that list down a
column, but I want it to not carry over any blank cells. So on sheet2 it
would look like this:

A
1 Bill
2 John
3 Mike
4 Rex
5 Stu

Do you know how I can make that happen? Thank you in advance.