Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtered cells return after saving
Excel 2007... After removing columns from a document Im left with rows that
have blanks in some of the fields. I click on the column that has blanks, go to data/ filter to uncheck blanks and then do a sort on the table to alphabetize the list thats left. I save the document thinking it all looks grteat. But when I reopen the document, the filter is not saved. the rows that have blank fields are back. Is it because there are other cells in the row that are not blank? Im confused. What good is filter? I need to filter the list to remove all rows with blanks in a particular column. None of the other data in the rest of the row that contains a blank matters. Why isnt the filter being saved? Any answers please????? Thanks, Susan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtered cells return after saving
Hello again, Susan,
as I advised you before, apply the filter to the appropriate column, and select Blanks from the bottom of the filter pull-down. This will show only those rows which have blanks in that column, which are the ones you want to get rid of. Then you should highlight all of the rows that are visible, i.e. click on the row identifier for the first visible row, hold down SHIFT, and click on the row identifier of the last visible row, then release SHIFT. These are the rows you want to delete, so while they are highlighted click on Edit | Delete Row - now you should see nothing below your headers, and you will need to click All from the filter pull-down in order to see the remaining rows. You can then sort these if you wish. NOTE that you have to delete the rows with blanks in them - it is not sufficient to just hide them by selecting Non-blanks from the filter pull-down, which is what I think you are doing. Now you should click the Save icon - I understand from your earlier post that you have started with a .csv file, and so at this stage Excel will warn you that you will lose some features if you save back as a .csv. You can go ahead with the save anyway, but if you want to avoid this message, then click on File | Save As and choose Excel workbook in the File Type box, give the file a suitable name (or keep it the same if you wish), then click the Save button. In this case your original file remains unchanged, so next time you should open the .xls (or .xlsx) file you have just saved, and that should have the rows with blank cells removed from it. You could then use File | Save As to save this as a .csv file - select .csv in the File Type box and give the file a suitable name. Hope this helps. Pete On Nov 18, 12:18 am, Susienak wrote: Excel 2007... After removing columns from a document Im left with rows that have blanks in some of the fields. I click on the column that has blanks, go to data/ filter to uncheck blanks and then do a sort on the table to alphabetize the list thats left. I save the document thinking it all looks grteat. But when I reopen the document, the filter is not saved. the rows that have blank fields are back. Is it because there are other cells in the row that are not blank? Im confused. What good is filter? I need to filter the list to remove all rows with blanks in a particular column. None of the other data in the rest of the row that contains a blank matters. Why isnt the filter being saved? Any answers please????? Thanks, Susan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtered cells return after saving
Hell again Pete...
Yes, I was unchecking just the blanks on the pull down. But a strange thing is going on doing it like you said. Say the list has 60,000 rows, and there are blanks in the first column, but none in the second column. When I do as you stated and deselect all and check only blanks, you said I will be left with only rows that have that blank cell in the first column. But thats not what Im experiencing. It is still showing the 60,000 rows, only now they are all blank in the first column and still have a filled cell in the secoind column. So I highlight all 60,000 rows and right click and delete rows (takes a long time waiting on Vista or Excel to process that) and then go back to the filter and select all...... and it works, but I thought it strange that all 60,000 rows were still there when selecting just blanks. Am I still doing something wrong? I thank you... your method did work other than its showing all 60,000 rows that I have to delete. Its why I didnt follow your instructions the first time... it didnt look right... it should have only showed about 8,000 rows, not all 60,000 or so I thought (so I thought I misunderstood you and was just supposed to uncheck blanks in the filter pulldown because at least that appeared to work). Anyway... thought I would relate this because it seems like a glitch... the novice Excel user thanks you (Im a marketing designer so I use Publisher and another graphic program, so Excel is a little foreign to me)..... and thanks for the advice on how to save the csv file. Susan "Pete_UK" wrote: Hello again, Susan, as I advised you before, apply the filter to the appropriate column, and select Blanks from the bottom of the filter pull-down. This will show only those rows which have blanks in that column, which are the ones you want to get rid of. Then you should highlight all of the rows that are visible, i.e. click on the row identifier for the first visible row, hold down SHIFT, and click on the row identifier of the last visible row, then release SHIFT. These are the rows you want to delete, so while they are highlighted click on Edit | Delete Row - now you should see nothing below your headers, and you will need to click All from the filter pull-down in order to see the remaining rows. You can then sort these if you wish. NOTE that you have to delete the rows with blanks in them - it is not sufficient to just hide them by selecting Non-blanks from the filter pull-down, which is what I think you are doing. Now you should click the Save icon - I understand from your earlier post that you have started with a .csv file, and so at this stage Excel will warn you that you will lose some features if you save back as a .csv. You can go ahead with the save anyway, but if you want to avoid this message, then click on File | Save As and choose Excel workbook in the File Type box, give the file a suitable name (or keep it the same if you wish), then click the Save button. In this case your original file remains unchanged, so next time you should open the .xls (or .xlsx) file you have just saved, and that should have the rows with blank cells removed from it. You could then use File | Save As to save this as a .csv file - select .csv in the File Type box and give the file a suitable name. Hope this helps. Pete On Nov 18, 12:18 am, Susienak wrote: Excel 2007... After removing columns from a document Im left with rows that have blanks in some of the fields. I click on the column that has blanks, go to data/ filter to uncheck blanks and then do a sort on the table to alphabetize the list thats left. I save the document thinking it all looks grteat. But when I reopen the document, the filter is not saved. the rows that have blank fields are back. Is it because there are other cells in the row that are not blank? Im confused. What good is filter? I need to filter the list to remove all rows with blanks in a particular column. None of the other data in the rest of the row that contains a blank matters. Why isnt the filter being saved? Any answers please????? Thanks, Susan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtered cells return after saving
Hi Suzie
When you are selecting Blanks, it will include the rows where there is data in other columns in those rows, as well as the remainder of the sheet which have not yet been used, as they will be blank also. You might be better to Sort the whole block of your data, by the column which contains blanks. This will cause all of the rows with blanks to go to the end of the list. Now delete all rows at the end of the list, where there is data in other columns (but not the one you sorted on). Then re-sort the whole table by whichever column you require for your sorted list. -- Regards Roger Govier "Susienak" wrote in message ... Excel 2007... After removing columns from a document Im left with rows that have blanks in some of the fields. I click on the column that has blanks, go to data/ filter to uncheck blanks and then do a sort on the table to alphabetize the list thats left. I save the document thinking it all looks grteat. But when I reopen the document, the filter is not saved. the rows that have blank fields are back. Is it because there are other cells in the row that are not blank? Im confused. What good is filter? I need to filter the list to remove all rows with blanks in a particular column. None of the other data in the rest of the row that contains a blank matters. Why isnt the filter being saved? Any answers please????? Thanks, Susan |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtered cells return after saving
Susan,
when you apply a filter the records not selected by the filter are still there - they are just not displayed. The row indicator will turn to blue to indicate that a filter is active, and if you look carefully you will see that some rows are missing in the sequence (i.e. those that do not match the filter criteria). As you are displaying 52,000 out of 60,000 rows, you could easily conclude that all the rows are being displayed, but I assure you that they are not. Deleting 52,000 rows will take some time, as you have discovered. If I had known that you had so many to delete I would have suggested an alternative, but I won't confuse you more with that now. Filters take a bit of getting used to, but maybe you should get some practise on smaller files. Anyway, you got it to work, and I'm glad to hear that. Pete On Nov 18, 6:14 am, Susienak wrote: Hell again Pete... Yes, I was unchecking just the blanks on the pull down. But a strange thing is going on doing it like you said. Say the list has 60,000 rows, and there are blanks in the first column, but none in the second column. When I do as you stated and deselect all and check only blanks, you said I will be left with only rows that have that blank cell in the first column. But thats not what Im experiencing. It is still showing the 60,000 rows, only now they are all blank in the first column and still have a filled cell in the secoind column. So I highlight all 60,000 rows and right click and delete rows (takes a long time waiting on Vista or Excel to process that) and then go back to the filter and select all...... and it works, but I thought it strange that all 60,000 rows were still there when selecting just blanks. Am I still doing something wrong? I thank you... your method did work other than its showing all 60,000 rows that I have to delete. Its why I didnt follow your instructions the first time... it didnt look right... it should have only showed about 8,000 rows, not all 60,000 or so I thought (so I thought I misunderstood you and was just supposed to uncheck blanks in the filter pulldown because at least that appeared to work). Anyway... thought I would relate this because it seems like a glitch... the novice Excel user thanks you (Im a marketing designer so I use Publisher and another graphic program, so Excel is a little foreign to me)..... and thanks for the advice on how to save the csv file. Susan "Pete_UK" wrote: Hello again, Susan, as I advised you before, apply the filter to the appropriate column, and select Blanks from the bottom of the filter pull-down. This will show only those rows which have blanks in that column, which are the ones you want to get rid of. Then you should highlight all of the rows that are visible, i.e. click on the row identifier for the first visible row, hold down SHIFT, and click on the row identifier of the last visible row, then release SHIFT. These are the rows you want to delete, so while they are highlighted click on Edit | Delete Row - now you should see nothing below your headers, and you will need to click All from the filter pull-down in order to see the remaining rows. You can then sort these if you wish. NOTE that you have to delete the rows with blanks in them - it is not sufficient to just hide them by selecting Non-blanks from the filter pull-down, which is what I think you are doing. Now you should click the Save icon - I understand from your earlier post that you have started with a .csv file, and so at this stage Excel will warn you that you will lose some features if you save back as a .csv. You can go ahead with the save anyway, but if you want to avoid this message, then click on File | Save As and choose Excel workbook in the File Type box, give the file a suitable name (or keep it the same if you wish), then click the Save button. In this case your original file remains unchanged, so next time you should open the .xls (or .xlsx) file you have just saved, and that should have the rows with blank cells removed from it. You could then use File | Save As to save this as a .csv file - select .csv in the File Type box and give the file a suitable name. Hope this helps. Pete On Nov 18, 12:18 am, Susienak wrote: Excel 2007... After removing columns from a document Im left with rows that have blanks in some of the fields. I click on the column that has blanks, go to data/ filter to uncheck blanks and then do a sort on the table to alphabetize the list thats left. I save the document thinking it all looks grteat. But when I reopen the document, the filter is not saved. the rows that have blank fields are back. Is it because there are other cells in the row that are not blank? Im confused. What good is filter? I need to filter the list to remove all rows with blanks in a particular column. None of the other data in the rest of the row that contains a blank matters. Why isnt the filter being saved? Any answers please????? Thanks, Susan- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Value from a Filtered List | Excel Worksheet Functions | |||
Referencing Filtered Cells | Excel Worksheet Functions | |||
filtered cells | Excel Discussion (Misc queries) | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Sumproduct on filtered cells | Excel Worksheet Functions |