Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
Finding a value and then suppressing ALL rows WITHOUT the found va
Excel 2003 SP3
WinXP Pro SP2 I need to find all entries in a specific column, say U, that have the text value of '30'. There are about 12K rows and about 150 or less have the text value of '30' in the particular 'Unnn' cell. Once I've found this text value of '30' (EditFindFind What=30Search by ColumnsMatch entire cell contents), I want to suppress all the other rows and print just columns A, B, C and D as well as U (which contains the text value of '30'). I know how to Hide rows but how do I hide those rows that do NOT have '30' without manually doing it? So: 1) Find all rows with text value '30' in Column U 2) Suppress all rows WITHOUT text value '30' in Column U 3) Print ONLY Columns A, B, C, D and U. Simple? I have no idea on the hiding aspect except a manual hide which is too laborious! TIA! |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
Finding a value and then suppressing ALL rows WITHOUT the foundva
Here's the manual way:
1. DataFilterAutofilter 2. Click arrow in column U 3. Choose '30' in the dropdown list -- now you are viewing only the rows that have a '30' in column U 4. Highlight visible rows and copy to new worksheet 5. Delete all columns except A,B,C,D,U 6. Goto FilePrint HTH, JP On Jan 23, 4:09*pm, Tom wrote: Excel 2003 SP3 WinXP Pro SP2 I need to find all entries in a specific column, say U, that have the text value of '30'. *There are about 12K rows and about 150 or less have the text value of '30' in the particular 'Unnn' cell. *Once I've found this text value of '30' (EditFindFind What=30Search by ColumnsMatch entire cell contents), I want to suppress all the other rows and print just columns A, B, C and D as well as U (which contains the text value of '30'). *I know how to Hide rows but how do I hide those rows that do NOT have '30' without manually doing it? So: 1) Find all rows with text value '30' in Column U 2) Suppress all rows WITHOUT text value '30' in Column U 3) Print ONLY Columns A, B, C, D and U. Simple? *I have no idea on the hiding aspect except a manual hide which is too laborious! TIA! |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
Finding a value and then suppressing ALL rows WITHOUT the found va
Tom
Select E:T and FormatHide. DataFilterAutofilter on column U to filter out anything not '30' FilePrint should get you only A:D and U with the '30' rows. Gord Dibben MS Excel MVP On Wed, 23 Jan 2008 13:09:03 -0800, Tom wrote: Excel 2003 SP3 WinXP Pro SP2 I need to find all entries in a specific column, say U, that have the text value of '30'. There are about 12K rows and about 150 or less have the text value of '30' in the particular 'Unnn' cell. Once I've found this text value of '30' (EditFindFind What=30Search by ColumnsMatch entire cell contents), I want to suppress all the other rows and print just columns A, B, C and D as well as U (which contains the text value of '30'). I know how to Hide rows but how do I hide those rows that do NOT have '30' without manually doing it? So: 1) Find all rows with text value '30' in Column U 2) Suppress all rows WITHOUT text value '30' in Column U 3) Print ONLY Columns A, B, C, D and U. Simple? I have no idea on the hiding aspect except a manual hide which is too laborious! TIA! |
#4
Posted to microsoft.public.excel.setup
|
|||
|
|||
Finding a value and then suppressing ALL rows WITHOUT the foundva
Yeah, what he said. :-)
--JP On Jan 23, 5:40*pm, Gord Dibben <gorddibbATshawDOTca wrote: Tom Select E:T and FormatHide. DataFilterAutofilter on column U to filter out anything not '30' FilePrint should get you only A:D and U with the '30' rows. Gord Dibben *MS Excel MVP |
#5
Posted to microsoft.public.excel.setup
|
|||
|
|||
Finding a value and then suppressing ALL rows WITHOUT the found va
Another method of hiding the desired columns without the need to hide them
manually on each occasion would be to manually hide them once, then define that as a Custom View. I would customize a tool bar or the Menu bar and place the Custom View icon there. Subsequently, all that would be needed would be to auto filter the desire rows, then select the Custom View name from the tool bar icon. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clarification: Finding/Listing Symbols Found in 2 Columns | Excel Worksheet Functions | |||
Finding a value within two rows | Excel Worksheet Functions | |||
Copying whole rows to different sheet once found | Excel Discussion (Misc queries) | |||
Copy Rows found using Find All feature | Excel Discussion (Misc queries) | |||
Hi all!! Suppressing the "Do you want to save changes" | Excel Worksheet Functions |