Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically condense a field of data to eliminate blank lines
I have a field of 30 lines by 3 columns. Based on other data on the
page none of the lines may have data while as many as 30 of the lines may have data. the data all flows into other areas and into charts. I need a way even if it means importing the data in to another worksheet or cells, to condense the data to eliminate the blank lines WITHOUT running a macro. Example: I need: A B C D 1 17642 1 78.44% TOTAL SALARIES & BENEFITS 2 468 4 2.08% 31 SUPPLIES 3 4 32 7 0.14% 33 SUPPLIES-ISSUED FROM INVENTORY 5 1160 3 5.16% 34 SERVICES 6 7 8 9 10 11 12 13 56 6 0.25% 3D TRAVEL OTHER THAN TRAINING 14 15 16 17 2692 2 11.97% 3H VEHICLE MAINTENANCE SERVICES 18 19 29 8 0.13% 3K CARFARE & TOLLS 20 21 22 23 24 25 411 5 1.83% 42 UTILITIES & HEATING FUEL 26 27 28 29 30 To turn into: A B C D 1 17642 1 78.44% TOTAL SALARIES & BENEFITS 2 468 4 2.08% 31 SUPPLIES 3 32 7 0.14% 33 SUPPLIES-ISSUED FROM INVENTORY 4 1160 3 5.16% 34 SERVICES 5 56 6 0.25% 3D TRAVEL OTHER THAN TRAINING 6 2692 2 11.97% 3H VEHICLE MAINTENANCE SERVICES 7 29 8 0.13% 3K CARFARE & TOLLS 8 411 5 1.83% 42 UTILITIES & HEATING FUEL I need this to occur automatically because it has to function for novice users that won't even know how to do a sort. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically condense a field of data to eliminate blank lines
On Apr 3, 3:23*pm, wrote:
I have a field of 30 lines by 3 columns. *Based on other data on the page none of the lines may have data while as many as 30 of the lines may have data. *the data all flows into other areas and into charts. I need a way even if it means importing the data in to another worksheet or cells, to condense the data to eliminate the blank lines WITHOUT running a macro. Example: *I need: * * * * A * * * B * * * C * * * D 1 * * * 17642 * 1 * * * 78.44% *TOTAL SALARIES & BENEFITS 2 * * * 468 * * 4 * * * 2.08% * 31 SUPPLIES 3 4 * * * 32 * * *7 * * * 0.14% * 33 SUPPLIES-ISSUED FROM INVENTORY 5 * * * 1160 * *3 * * * 5.16% * 34 SERVICES 6 7 8 9 10 11 12 13 * * *56 * * *6 * * * 0.25% * 3D TRAVEL OTHER THAN TRAINING 14 15 16 17 * * *2692 * *2 * * * 11.97% *3H VEHICLE MAINTENANCE SERVICES 18 19 * * *29 * * *8 * * * 0.13% * 3K CARFARE & TOLLS 20 21 22 23 24 25 * * *411 * * 5 * * * 1.83% * 42 UTILITIES & HEATING FUEL 26 27 28 29 30 To turn into: * * * * A * * * B * * * C * * * D 1 * * * 17642 * 1 * * * 78.44% *TOTAL SALARIES & BENEFITS 2 * * * 468 * * 4 * * * 2.08% * 31 SUPPLIES 3 * * * 32 * * *7 * * * 0.14% * 33 SUPPLIES-ISSUED FROM INVENTORY 4 * * * 1160 * *3 * * * 5.16% * 34 SERVICES 5 * * * 56 * * *6 * * * 0.25% * 3D TRAVEL OTHER THAN TRAINING 6 * * * 2692 * *2 * * * 11.97% *3H VEHICLE MAINTENANCE SERVICES 7 * * * 29 * * *8 * * * 0.13% * 3K CARFARE & TOLLS 8 * * * 411 * * 5 * * * 1.83% * 42 UTILITIES & HEATING FUEL I need this to occur automatically because it has to function for novice users that won't even know how to do a sort. Don: I guess the easist way to do this would be to go to Data / Filter / Autofilter. Make sure your cursor is on A1. Then in the drop-down box of the auto-filter, select (Non Blanks), and you will get ONLY those rows, which are non-blanks. Regards, S. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically condense a field of data to eliminate blank lines
On Apr 3, 4:56*pm, Sam wrote:
On Apr 3, 3:23*pm, wrote: I have a field of 30 lines by 3 columns. *Based on other data on the page none of the lines may have data while as many as 30 of the lines may have data. *the data all flows into other areas and into charts. I need a way even if it means importing the data in to another worksheet or cells, to condense the data to eliminate the blank lines WITHOUT running a macro. Example: *I need: * * * * A * * * B * * * C * * * D 1 * * * 17642 * 1 * * * 78.44% *TOTAL SALARIES & BENEFITS 2 * * * 468 * * 4 * * * 2.08% * 31 SUPPLIES 3 4 * * * 32 * * *7 * * * 0.14% * 33 SUPPLIES-ISSUED FROM INVENTORY 5 * * * 1160 * *3 * * * 5.16% * 34 SERVICES 6 7 8 9 10 11 12 13 * * *56 * * *6 * * * 0.25% * 3D TRAVEL OTHER THAN TRAINING 14 15 16 17 * * *2692 * *2 * * * 11.97% *3H VEHICLE MAINTENANCE SERVICES 18 19 * * *29 * * *8 * * * 0.13% * 3K CARFARE & TOLLS 20 21 22 23 24 25 * * *411 * * 5 * * * 1.83% * 42 UTILITIES & HEATING FUEL 26 27 28 29 30 To turn into: * * * * A * * * B * * * C * * * D 1 * * * 17642 * 1 * * * 78.44% *TOTAL SALARIES & BENEFITS 2 * * * 468 * * 4 * * * 2.08% * 31 SUPPLIES 3 * * * 32 * * *7 * * * 0.14% * 33 SUPPLIES-ISSUED FROM INVENTORY 4 * * * 1160 * *3 * * * 5.16% * 34 SERVICES 5 * * * 56 * * *6 * * * 0.25% * 3D TRAVEL OTHER THAN TRAINING 6 * * * 2692 * *2 * * * 11.97% *3H VEHICLE MAINTENANCE SERVICES 7 * * * 29 * * *8 * * * 0.13% * 3K CARFARE & TOLLS 8 * * * 411 * * 5 * * * 1.83% * 42 UTILITIES & HEATING FUEL I need this to occur automatically because it has to function for novice users that won't even know how to do a sort. Don: I guess the easist way to do this would be to go to Data / Filter / Autofilter. Make sure your cursor is on A1. Then in the drop-down box of the auto-filter, select (Non Blanks), and you will get ONLY those rows, which are non-blanks. Regards, S.- Hide quoted text - - Show quoted text - This won't work. It needs to be an automated process and this is only a very small portion of a much larger spreadsheet. I can't apply a process that would affect the remainder of the worksheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filtering: sometimes data below blank lines is &isnt available? | Excel Worksheet Functions | |||
I want to eliminate the first character in several lines | Excel Discussion (Misc queries) | |||
how to I sort and keep blank lines in between data | Excel Programming | |||
how to eliminate a space when the field is blank in a merge | Excel Discussion (Misc queries) | |||
how to automatically insert blank lines in between non-blank lines | Excel Programming |