Copy a column leaving out any blanks

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
Edit>Fill>Series>columns>linear>stepvalue = 1>ok.

Regards
FSt1
 
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
 
=IF(ISERR(SMALL(IF(names<>"",ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))),"",INDEX(names,SMALL(IF(names<>"",ROW(INDIRECT("1:"&ROWS(names)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down
 
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(Sheet1!$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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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.
 
Back
Top