Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use formula to remove blank entries from a column
I have a single column of data that is sparse, in that many of the entries
are blank. Since this data is imported from another source, I want to operate on it with a formula and list the non-blank entries on another page. Thus if there are 1000 lines of data, but only 20 of them are non-blank, the new column would have just the 20 non-blank entries listed, with no blank lines in between. I don't want to use filters or sorting, since this needs to be an automatic process once the data is updated. I suspect I can do this with the index function and treat the column as an array, but I just can't figure out the right formula. Any ideas? Thanks, Pete |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use formula to remove blank entries from a column
Try this:
Data in the range Sheet1 A1:A1000 On Sheet2 enter this formula in A1: =COUNTA(Sheet1!A:A) That will return the count of non-empty cells from Sheet1 A1:A1000. Enter this array formula in A2: =IF(ROWS(A$2:A2)<=A$1,INDEX(Sheet1!A$1:A$1000,SMAL L(IF(Sheet1!A$1:A$1000<"",ROW(Sheet1!A$1:A$1000)) ,ROWS(A$2:A2))-ROW(Sheet1!A$1)+1),"") ** 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. Copy down until you get blanks. If the number of non-empty cells varies then you'll have to copy to a range big enough to ensure you get all the expected results. Only you know how many results you typically expect. -- Biff Microsoft Excel MVP "Pete J" <Pete wrote in message ... I have a single column of data that is sparse, in that many of the entries are blank. Since this data is imported from another source, I want to operate on it with a formula and list the non-blank entries on another page. Thus if there are 1000 lines of data, but only 20 of them are non-blank, the new column would have just the 20 non-blank entries listed, with no blank lines in between. I don't want to use filters or sorting, since this needs to be an automatic process once the data is updated. I suspect I can do this with the index function and treat the column as an array, but I just can't figure out the right formula. Any ideas? Thanks, Pete |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use formula to remove blank entries from a column
If you dont have duplicate entries; try this..Please note that this is an
array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" Your data in Col A.. B1 =INDEX(A1:A100,MIN(IF(A1:A100="","",ROW(A1:A100))) ) B2 (all in one line) =IF(COUNTA($A$1:$A$100)=ROW(),INDEX($A$1:$A$100,M IN(IF(INDIRECT("A" & MATCH(B1,$A$1:$A$100,0)+1 &":A100")="","",ROW(INDIRECT("A" & MATCH(B1,$A$1:$A$100,0)+1 & ":A100"))))),"") and copy that down as required... If this post helps click Yes --------------- Jacob Skaria "Pete J" wrote: I have a single column of data that is sparse, in that many of the entries are blank. Since this data is imported from another source, I want to operate on it with a formula and list the non-blank entries on another page. Thus if there are 1000 lines of data, but only 20 of them are non-blank, the new column would have just the 20 non-blank entries listed, with no blank lines in between. I don't want to use filters or sorting, since this needs to be an automatic process once the data is updated. I suspect I can do this with the index function and treat the column as an array, but I just can't figure out the right formula. Any ideas? Thanks, Pete |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use formula to remove blank entries from a column
You won't accomplish that with just a formula. You can do it easily with a
number of different macros, but they all involve a method you didn't want to do. -- -John Please rate when your question is answered to help us and others know what is helpful. "Pete J" wrote: I have a single column of data that is sparse, in that many of the entries are blank. Since this data is imported from another source, I want to operate on it with a formula and list the non-blank entries on another page. Thus if there are 1000 lines of data, but only 20 of them are non-blank, the new column would have just the 20 non-blank entries listed, with no blank lines in between. I don't want to use filters or sorting, since this needs to be an automatic process once the data is updated. I suspect I can do this with the index function and treat the column as an array, but I just can't figure out the right formula. Any ideas? Thanks, Pete |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use formula to remove blank entries from a column
Thanks everyone. I tried all the ideas here, and the one that seems to work
best for me is this one. Problem solved! "T. Valko" wrote: Try this: Data in the range Sheet1 A1:A1000 On Sheet2 enter this formula in A1: =COUNTA(Sheet1!A:A) That will return the count of non-empty cells from Sheet1 A1:A1000. Enter this array formula in A2: =IF(ROWS(A$2:A2)<=A$1,INDEX(Sheet1!A$1:A$1000,SMAL L(IF(Sheet1!A$1:A$1000<"",ROW(Sheet1!A$1:A$1000)) ,ROWS(A$2:A2))-ROW(Sheet1!A$1)+1),"") ** 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. Copy down until you get blanks. If the number of non-empty cells varies then you'll have to copy to a range big enough to ensure you get all the expected results. Only you know how many results you typically expect. -- Biff Microsoft Excel MVP "Pete J" <Pete wrote in message ... I have a single column of data that is sparse, in that many of the entries are blank. Since this data is imported from another source, I want to operate on it with a formula and list the non-blank entries on another page. Thus if there are 1000 lines of data, but only 20 of them are non-blank, the new column would have just the 20 non-blank entries listed, with no blank lines in between. I don't want to use filters or sorting, since this needs to be an automatic process once the data is updated. I suspect I can do this with the index function and treat the column as an array, but I just can't figure out the right formula. Any ideas? Thanks, Pete |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use formula to remove blank entries from a column
John
If I was rating your answer I would give it a low rating. There are several ways to do this using a formula. Gord Dibben MS Excel MVP On Thu, 18 Jun 2009 09:13:09 -0700, John Bundy (remove) wrote: You won't accomplish that with just a formula. You can do it easily with a number of different macros, but they all involve a method you didn't want to do. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use formula to remove blank entries from a column
It's usually a bad idea to say that something can't be done. I know this
from my own experience! <BG -- Biff Microsoft Excel MVP "Gord Dibben" <gorddibbATshawDOTca wrote in message ... John If I was rating your answer I would give it a low rating. There are several ways to do this using a formula. Gord Dibben MS Excel MVP On Thu, 18 Jun 2009 09:13:09 -0700, John Bundy (remove) wrote: You won't accomplish that with just a formula. You can do it easily with a number of different macros, but they all involve a method you didn't want to do. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use formula to remove blank entries from a column
Oh yeah!
I know all about that<g But I don't ask to be rated..........when the readers decide I'm full of s**t they will let me know by ignoring my posts. Is that why I don't see many responses? Gord On Thu, 18 Jun 2009 14:35:10 -0400, "T. Valko" wrote: It's usually a bad idea to say that something can't be done. I know this from my own experience! <BG |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove extra blank lines in a column when I print? | Excel Discussion (Misc queries) | |||
How to remove duplicate entries in column? | Excel Discussion (Misc queries) | |||
Remove link fr a column of entries | Excel Discussion (Misc queries) | |||
Remove link fr a column of entries | Excel Discussion (Misc queries) | |||
Remove link fr a column of entries | Excel Discussion (Misc queries) |