Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy a list and skipping blanks | Excel Worksheet Functions | |||
copy range of cells with blanks then paste without blanks | Excel Worksheet Functions | |||
Copy cells to neighbor column after leaving the cell | Excel Worksheet Functions | |||
Copy only celss with text not blanks | Excel Discussion (Misc queries) | |||
Replace all non blanks in a column with column header | Excel Discussion (Misc queries) |