Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
gang remove blank rows
I have a large amount of data that is composed of a text row followed by 2
blank rows. This pattern repeats itself in the entire worksheet. I want to gang remove the blank rows so that I end up with single spaced data. Any help out there. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
gang remove blank rows
Can you just sort your data?
swell estimator wrote: I have a large amount of data that is composed of a text row followed by 2 blank rows. This pattern repeats itself in the entire worksheet. I want to gang remove the blank rows so that I end up with single spaced data. Any help out there. Thank you. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
gang remove blank rows
Dave, the sort function was the first thing I tried but ascending and
descending compresses the row and removes the original blank rows, but doesn't keep the original order. Pls look at the following and imagine that before I manually removed them, each row had 2 blank rows between them. These are window openings with the width as the first factor and height 2nd factor 1'(w) x 1'(h) . The 2nd # is the constant and the 1st # the variable in multiples of ten and they have to remain in the original order in order to be coherent to someone choosing a window width and height from a list. thanks for help. 1' x 1' - 8' tall wall, 16" o.c. 2' x 1' - 8' tall wall, 16" o.c. 3' x 1' - 8' tall wall, 16" o.c. 4' x 1' - 8' tall wall, 16" o.c. 5' x 1' - 8' tall wall, 16" o.c. 6' x 1' - 8' tall wall, 16" o.c. 7' x 1' - 8' tall wall, 16" o.c. 8' x 1' - 8' tall wall, 16" o.c. 9' x 1' - 8' tall wall, 16" o.c. 10' x 1' - 8' tall wall, 16" o.c. 1' x 2' - 8' tall wall, 16" o.c. 2' x 2' - 8' tall wall, 16" o.c. 3' x 2' - 8' tall wall, 16" o.c. 4' x 2' - 8' tall wall, 16" o.c. 5' x 2' - 8' tall wall, 16" o.c. 6' x 2' - 8' tall wall, 16" o.c. 7' x 2' - 8' tall wall, 16" o.c. 8' x 2' - 8' tall wall, 16" o.c. 9' x 2' - 8' tall wall, 16" o.c. 10' x 2' - 8' tall wall, 16" o.c. 1' x 3' - 8' tall wall, 16" o.c. 2' x 3' - 8' tall wall, 16" o.c..... ~ ~ "swell estimator" wrote: I have a large amount of data that is composed of a text row followed by 2 blank rows. This pattern repeats itself in the entire worksheet. I want to gang remove the blank rows so that I end up with single spaced data. Any help out there. Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
gang remove blank rows
Add an extra helper column.
Fill it with formulas like: =row() then convert those formulas to values Do your sort. Delete the blank rows if they're not at the bottom. sort the remaining data by this helper column delete the helper column. swell estimator wrote: Dave, the sort function was the first thing I tried but ascending and descending compresses the row and removes the original blank rows, but doesn't keep the original order. Pls look at the following and imagine that before I manually removed them, each row had 2 blank rows between them. These are window openings with the width as the first factor and height 2nd factor 1'(w) x 1'(h) . The 2nd # is the constant and the 1st # the variable in multiples of ten and they have to remain in the original order in order to be coherent to someone choosing a window width and height from a list. thanks for help. 1' x 1' - 8' tall wall, 16" o.c. 2' x 1' - 8' tall wall, 16" o.c. 3' x 1' - 8' tall wall, 16" o.c. 4' x 1' - 8' tall wall, 16" o.c. 5' x 1' - 8' tall wall, 16" o.c. 6' x 1' - 8' tall wall, 16" o.c. 7' x 1' - 8' tall wall, 16" o.c. 8' x 1' - 8' tall wall, 16" o.c. 9' x 1' - 8' tall wall, 16" o.c. 10' x 1' - 8' tall wall, 16" o.c. 1' x 2' - 8' tall wall, 16" o.c. 2' x 2' - 8' tall wall, 16" o.c. 3' x 2' - 8' tall wall, 16" o.c. 4' x 2' - 8' tall wall, 16" o.c. 5' x 2' - 8' tall wall, 16" o.c. 6' x 2' - 8' tall wall, 16" o.c. 7' x 2' - 8' tall wall, 16" o.c. 8' x 2' - 8' tall wall, 16" o.c. 9' x 2' - 8' tall wall, 16" o.c. 10' x 2' - 8' tall wall, 16" o.c. 1' x 3' - 8' tall wall, 16" o.c. 2' x 3' - 8' tall wall, 16" o.c..... ~ ~ "swell estimator" wrote: I have a large amount of data that is composed of a text row followed by 2 blank rows. This pattern repeats itself in the entire worksheet. I want to gang remove the blank rows so that I end up with single spaced data. Any help out there. Thank you. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
gang remove blank rows
Dave, thank you for replying. I tired everything I could think of using a
helper column entering =row() in the first cell of the helper column copying down, tried some other things like puting in the cell reference too but nada. Pls step me thru it in detail. Precisely what do I put in the 1st cell of the helper column and the cells below and what do you mean about converting those formulas to values. thanks, Chris "Dave Peterson" wrote: Add an extra helper column. Fill it with formulas like: =row() then convert those formulas to values Do your sort. Delete the blank rows if they're not at the bottom. sort the remaining data by this helper column delete the helper column. swell estimator wrote: Dave, the sort function was the first thing I tried but ascending and descending compresses the row and removes the original blank rows, but doesn't keep the original order. Pls look at the following and imagine that before I manually removed them, each row had 2 blank rows between them. These are window openings with the width as the first factor and height 2nd factor 1'(w) x 1'(h) . The 2nd # is the constant and the 1st # the variable in multiples of ten and they have to remain in the original order in order to be coherent to someone choosing a window width and height from a list. thanks for help. 1' x 1' - 8' tall wall, 16" o.c. 2' x 1' - 8' tall wall, 16" o.c. 3' x 1' - 8' tall wall, 16" o.c. 4' x 1' - 8' tall wall, 16" o.c. 5' x 1' - 8' tall wall, 16" o.c. 6' x 1' - 8' tall wall, 16" o.c. 7' x 1' - 8' tall wall, 16" o.c. 8' x 1' - 8' tall wall, 16" o.c. 9' x 1' - 8' tall wall, 16" o.c. 10' x 1' - 8' tall wall, 16" o.c. 1' x 2' - 8' tall wall, 16" o.c. 2' x 2' - 8' tall wall, 16" o.c. 3' x 2' - 8' tall wall, 16" o.c. 4' x 2' - 8' tall wall, 16" o.c. 5' x 2' - 8' tall wall, 16" o.c. 6' x 2' - 8' tall wall, 16" o.c. 7' x 2' - 8' tall wall, 16" o.c. 8' x 2' - 8' tall wall, 16" o.c. 9' x 2' - 8' tall wall, 16" o.c. 10' x 2' - 8' tall wall, 16" o.c. 1' x 3' - 8' tall wall, 16" o.c. 2' x 3' - 8' tall wall, 16" o.c..... ~ ~ "swell estimator" wrote: I have a large amount of data that is composed of a text row followed by 2 blank rows. This pattern repeats itself in the entire worksheet. I want to gang remove the blank rows so that I end up with single spaced data. Any help out there. Thank you. -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
gang remove blank rows
Insert a new column A
Say your last row with data in it is row 777. Select A1:A777 type this: =row() and hit ctrl-enter This will fill that selected range with that same formula. You should see: 1,2,3, ..., 777. With A1:A777 still selected, rightclick on the selected area and choose Copy. Rightclick again and choose Paste Special Choose Values Now those formulas should be values. You can select your range (Say A1:X777) and do your sorting. Then delete the empty rows. Then delete column A. swell estimator wrote: Dave, thank you for replying. I tired everything I could think of using a helper column entering =row() in the first cell of the helper column copying down, tried some other things like puting in the cell reference too but nada. Pls step me thru it in detail. Precisely what do I put in the 1st cell of the helper column and the cells below and what do you mean about converting those formulas to values. thanks, Chris "Dave Peterson" wrote: Add an extra helper column. Fill it with formulas like: =row() then convert those formulas to values Do your sort. Delete the blank rows if they're not at the bottom. sort the remaining data by this helper column delete the helper column. swell estimator wrote: Dave, the sort function was the first thing I tried but ascending and descending compresses the row and removes the original blank rows, but doesn't keep the original order. Pls look at the following and imagine that before I manually removed them, each row had 2 blank rows between them. These are window openings with the width as the first factor and height 2nd factor 1'(w) x 1'(h) . The 2nd # is the constant and the 1st # the variable in multiples of ten and they have to remain in the original order in order to be coherent to someone choosing a window width and height from a list. thanks for help. 1' x 1' - 8' tall wall, 16" o.c. 2' x 1' - 8' tall wall, 16" o.c. 3' x 1' - 8' tall wall, 16" o.c. 4' x 1' - 8' tall wall, 16" o.c. 5' x 1' - 8' tall wall, 16" o.c. 6' x 1' - 8' tall wall, 16" o.c. 7' x 1' - 8' tall wall, 16" o.c. 8' x 1' - 8' tall wall, 16" o.c. 9' x 1' - 8' tall wall, 16" o.c. 10' x 1' - 8' tall wall, 16" o.c. 1' x 2' - 8' tall wall, 16" o.c. 2' x 2' - 8' tall wall, 16" o.c. 3' x 2' - 8' tall wall, 16" o.c. 4' x 2' - 8' tall wall, 16" o.c. 5' x 2' - 8' tall wall, 16" o.c. 6' x 2' - 8' tall wall, 16" o.c. 7' x 2' - 8' tall wall, 16" o.c. 8' x 2' - 8' tall wall, 16" o.c. 9' x 2' - 8' tall wall, 16" o.c. 10' x 2' - 8' tall wall, 16" o.c. 1' x 3' - 8' tall wall, 16" o.c. 2' x 3' - 8' tall wall, 16" o.c..... ~ ~ "swell estimator" wrote: I have a large amount of data that is composed of a text row followed by 2 blank rows. This pattern repeats itself in the entire worksheet. I want to gang remove the blank rows so that I end up with single spaced data. Any help out there. Thank you. -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
gang remove blank rows
Dave , the () in =row() is a double paren correct?
Here's how I set up a trial of this that I could not get to work. I used a portion of my data and copied it into column A using 208 rows, 1/3 of which was the text formerly listed with 2/3's of the rows blank. I then inserted a new column to the left of my column and that became column A and my original now column B. I selected all 208 cells in column A only, typed =row() in the top cell and hit ctrl,enter and the column filled with #'s from 1 to 208. While column A1-A208was selected I right-clicked on copy and again right clicked on paste special. I then selected both columns A and B to rows 1 - 208, accessed Data and clicked on Sort. The dialog button gave me the choice of column A or B. Column A didn't do anything but selecting column B compressed B1- B208 in the same way as previously when I just hit ascending on the tool bar. bers were not in the correct order. They were in this order: 1' x 1' - 9' tall wall, 24" o.c. 1' x 2' - 9' tall wall, 24" o.c. 1' x 3' - 9' tall wall, 24" o.c. 1' x 4' - 9' tall wall, 24" o.c. 1' x 5' - 9' tall wall, 24" o.c. 1' x 6' - 9' tall wall, 24" o.c. 1' x 7' - 9' tall wall, 24" o.c. 10' x 1' - 9' tall wall, 24" o.c. 10' x 2' - 9' tall wall, 24" o.c. 10' x 3' - 9' tall wall, 24" o.c. 10' x 4' - 9' tall wall, 24" o.c. 10' x 5' - 9' tall wall, 24" o.c. 10' x 6' - 9' tall wall, 24" o.c. 10' x 7' - 9' tall wall, 24" o.c. 2' x 1' - 9' tall wall, 24" o.c. 2' x 2' - 9' tall wall, 24" o.c. 2' x 3' - 9' tall wall, 24" o.c. 2' x 4' - 9' tall wall, 24" o.c. 2' x 5' - 9' tall wall, 24" o.c. 2' x 6' - 9' tall wall, 24" o.c. What did I do wrong? "Dave Peterson" wrote: Insert a new column A Say your last row with data in it is row 777. Select A1:A777 type this: =row() and hit ctrl-enter This will fill that selected range with that same formula. You should see: 1,2,3, ..., 777. With A1:A777 still selected, rightclick on the selected area and choose Copy. Rightclick again and choose Paste Special Choose Values Now those formulas should be values. You can select your range (Say A1:X777) and do your sorting. Then delete the empty rows. Then delete column A. swell estimator wrote: Dave, thank you for replying. I tired everything I could think of using a helper column entering =row() in the first cell of the helper column copying down, tried some other things like puting in the cell reference too but nada. Pls step me thru it in detail. Precisely what do I put in the 1st cell of the helper column and the cells below and what do you mean about converting those formulas to values. thanks, Chris "Dave Peterson" wrote: Add an extra helper column. Fill it with formulas like: =row() then convert those formulas to values Do your sort. Delete the blank rows if they're not at the bottom. sort the remaining data by this helper column delete the helper column. swell estimator wrote: Dave, the sort function was the first thing I tried but ascending and descending compresses the row and removes the original blank rows, but doesn't keep the original order. Pls look at the following and imagine that before I manually removed them, each row had 2 blank rows between them. These are window openings with the width as the first factor and height 2nd factor 1'(w) x 1'(h) . The 2nd # is the constant and the 1st # the variable in multiples of ten and they have to remain in the original order in order to be coherent to someone choosing a window width and height from a list. thanks for help. 1' x 1' - 8' tall wall, 16" o.c. 2' x 1' - 8' tall wall, 16" o.c. 3' x 1' - 8' tall wall, 16" o.c. 4' x 1' - 8' tall wall, 16" o.c. 5' x 1' - 8' tall wall, 16" o.c. 6' x 1' - 8' tall wall, 16" o.c. 7' x 1' - 8' tall wall, 16" o.c. 8' x 1' - 8' tall wall, 16" o.c. 9' x 1' - 8' tall wall, 16" o.c. 10' x 1' - 8' tall wall, 16" o.c. 1' x 2' - 8' tall wall, 16" o.c. 2' x 2' - 8' tall wall, 16" o.c. 3' x 2' - 8' tall wall, 16" o.c. 4' x 2' - 8' tall wall, 16" o.c. 5' x 2' - 8' tall wall, 16" o.c. 6' x 2' - 8' tall wall, 16" o.c. 7' x 2' - 8' tall wall, 16" o.c. 8' x 2' - 8' tall wall, 16" o.c. 9' x 2' - 8' tall wall, 16" o.c. 10' x 2' - 8' tall wall, 16" o.c. 1' x 3' - 8' tall wall, 16" o.c. 2' x 3' - 8' tall wall, 16" o.c..... ~ ~ "swell estimator" wrote: I have a large amount of data that is composed of a text row followed by 2 blank rows. This pattern repeats itself in the entire worksheet. I want to gang remove the blank rows so that I end up with single spaced data. Any help out there. Thank you. -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
gang remove blank rows
Dave , the () in =row() is a double paren correct?
Here's how I set up a trial of this that I could not get to work. I used a portion of my data and copied it into column A using 208 rows, 1/3 of which was the text formerly listed with 2/3's of the rows blank. I then inserted a new column to the left of my column and that became column A and my original now column B. I selected all 208 cells in column A only, typed =row() in the top cell and hit ctrl,enter and the column filled with #'s from 1 to 208. While column A1-A208was selected I right-clicked on copy and again right clicked on paste special. I then selected both columns A and B to rows 1 - 208, accessed Data and clicked on Sort. The dialog button gave me the choice of column A or B. Column A didn't do anything but selecting column B compressed B1- B208 in the same way as previously when I just hit ascending on the tool bar. bers were not in the correct order. They were in this order: 1' x 1' - 9' tall wall, 24" o.c. 1' x 2' - 9' tall wall, 24" o.c. 1' x 3' - 9' tall wall, 24" o.c. 1' x 4' - 9' tall wall, 24" o.c. 1' x 5' - 9' tall wall, 24" o.c. 1' x 6' - 9' tall wall, 24" o.c. 1' x 7' - 9' tall wall, 24" o.c. 10' x 1' - 9' tall wall, 24" o.c. 10' x 2' - 9' tall wall, 24" o.c. 10' x 3' - 9' tall wall, 24" o.c. 10' x 4' - 9' tall wall, 24" o.c. 10' x 5' - 9' tall wall, 24" o.c. 10' x 6' - 9' tall wall, 24" o.c. 10' x 7' - 9' tall wall, 24" o.c. 2' x 1' - 9' tall wall, 24" o.c. 2' x 2' - 9' tall wall, 24" o.c. 2' x 3' - 9' tall wall, 24" o.c. 2' x 4' - 9' tall wall, 24" o.c. 2' x 5' - 9' tall wall, 24" o.c. 2' x 6' - 9' tall wall, 24" o.c. What did I do wrong? "Dave Peterson" wrote: Insert a new column A Say your last row with data in it is row 777. Select A1:A777 type this: =row() and hit ctrl-enter This will fill that selected range with that same formula. You should see: 1,2,3, ..., 777. With A1:A777 still selected, rightclick on the selected area and choose Copy. Rightclick again and choose Paste Special Choose Values Now those formulas should be values. You can select your range (Say A1:X777) and do your sorting. Then delete the empty rows. Then delete column A. swell estimator wrote: Dave, thank you for replying. I tired everything I could think of using a helper column entering =row() in the first cell of the helper column copying down, tried some other things like puting in the cell reference too but nada. Pls step me thru it in detail. Precisely what do I put in the 1st cell of the helper column and the cells below and what do you mean about converting those formulas to values. thanks, Chris "Dave Peterson" wrote: Add an extra helper column. Fill it with formulas like: =row() then convert those formulas to values Do your sort. Delete the blank rows if they're not at the bottom. sort the remaining data by this helper column delete the helper column. swell estimator wrote: Dave, the sort function was the first thing I tried but ascending and descending compresses the row and removes the original blank rows, but doesn't keep the original order. Pls look at the following and imagine that before I manually removed them, each row had 2 blank rows between them. These are window openings with the width as the first factor and height 2nd factor 1'(w) x 1'(h) . The 2nd # is the constant and the 1st # the variable in multiples of ten and they have to remain in the original order in order to be coherent to someone choosing a window width and height from a list. thanks for help. 1' x 1' - 8' tall wall, 16" o.c. 2' x 1' - 8' tall wall, 16" o.c. 3' x 1' - 8' tall wall, 16" o.c. 4' x 1' - 8' tall wall, 16" o.c. 5' x 1' - 8' tall wall, 16" o.c. 6' x 1' - 8' tall wall, 16" o.c. 7' x 1' - 8' tall wall, 16" o.c. 8' x 1' - 8' tall wall, 16" o.c. 9' x 1' - 8' tall wall, 16" o.c. 10' x 1' - 8' tall wall, 16" o.c. 1' x 2' - 8' tall wall, 16" o.c. 2' x 2' - 8' tall wall, 16" o.c. 3' x 2' - 8' tall wall, 16" o.c. 4' x 2' - 8' tall wall, 16" o.c. 5' x 2' - 8' tall wall, 16" o.c. 6' x 2' - 8' tall wall, 16" o.c. 7' x 2' - 8' tall wall, 16" o.c. 8' x 2' - 8' tall wall, 16" o.c. 9' x 2' - 8' tall wall, 16" o.c. 10' x 2' - 8' tall wall, 16" o.c. 1' x 3' - 8' tall wall, 16" o.c. 2' x 3' - 8' tall wall, 16" o.c..... ~ ~ "swell estimator" wrote: I have a large amount of data that is composed of a text row followed by 2 blank rows. This pattern repeats itself in the entire worksheet. I want to gang remove the blank rows so that I end up with single spaced data. Any help out there. Thank you. -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
gang remove blank rows
Use DataFilter. Select Blanks. Now only blank rows will be displayed.
Select these rows, right-click on any row, select Delete Row. Regards, Fred. "swell estimator" wrote in message ... I have a large amount of data that is composed of a text row followed by 2 blank rows. This pattern repeats itself in the entire worksheet. I want to gang remove the blank rows so that I end up with single spaced data. Any help out there. Thank you. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
gang remove blank rows
Hi,
You can do this just using the first column. Select all of your first column, Tap F5 Click on Special Check Blanks Click OK Then right click on the selected Blanks Click Delete And check Entire rows HTH Martin "swell estimator" wrote: I have a large amount of data that is composed of a text row followed by 2 blank rows. This pattern repeats itself in the entire worksheet. I want to gang remove the blank rows so that I end up with single spaced data. Any help out there. Thank you. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
gang remove blank rows
Yep. =row() is the correct formula.
Sort your data to get put the empty rows together. then delete those empty rows Sort your data another time by column A. delete column A swell estimator wrote: Dave , the () in =row() is a double paren correct? Here's how I set up a trial of this that I could not get to work. I used a portion of my data and copied it into column A using 208 rows, 1/3 of which was the text formerly listed with 2/3's of the rows blank. I then inserted a new column to the left of my column and that became column A and my original now column B. I selected all 208 cells in column A only, typed =row() in the top cell and hit ctrl,enter and the column filled with #'s from 1 to 208. While column A1-A208was selected I right-clicked on copy and again right clicked on paste special. I then selected both columns A and B to rows 1 - 208, accessed Data and clicked on Sort. The dialog button gave me the choice of column A or B. Column A didn't do anything but selecting column B compressed B1- B208 in the same way as previously when I just hit ascending on the tool bar. bers were not in the correct order. They were in this order: 1' x 1' - 9' tall wall, 24" o.c. 1' x 2' - 9' tall wall, 24" o.c. 1' x 3' - 9' tall wall, 24" o.c. 1' x 4' - 9' tall wall, 24" o.c. 1' x 5' - 9' tall wall, 24" o.c. 1' x 6' - 9' tall wall, 24" o.c. 1' x 7' - 9' tall wall, 24" o.c. 10' x 1' - 9' tall wall, 24" o.c. 10' x 2' - 9' tall wall, 24" o.c. 10' x 3' - 9' tall wall, 24" o.c. 10' x 4' - 9' tall wall, 24" o.c. 10' x 5' - 9' tall wall, 24" o.c. 10' x 6' - 9' tall wall, 24" o.c. 10' x 7' - 9' tall wall, 24" o.c. 2' x 1' - 9' tall wall, 24" o.c. 2' x 2' - 9' tall wall, 24" o.c. 2' x 3' - 9' tall wall, 24" o.c. 2' x 4' - 9' tall wall, 24" o.c. 2' x 5' - 9' tall wall, 24" o.c. 2' x 6' - 9' tall wall, 24" o.c. What did I do wrong? "Dave Peterson" wrote: Insert a new column A Say your last row with data in it is row 777. Select A1:A777 type this: =row() and hit ctrl-enter This will fill that selected range with that same formula. You should see: 1,2,3, ..., 777. With A1:A777 still selected, rightclick on the selected area and choose Copy. Rightclick again and choose Paste Special Choose Values Now those formulas should be values. You can select your range (Say A1:X777) and do your sorting. Then delete the empty rows. Then delete column A. swell estimator wrote: Dave, thank you for replying. I tired everything I could think of using a helper column entering =row() in the first cell of the helper column copying down, tried some other things like puting in the cell reference too but nada. Pls step me thru it in detail. Precisely what do I put in the 1st cell of the helper column and the cells below and what do you mean about converting those formulas to values. thanks, Chris "Dave Peterson" wrote: Add an extra helper column. Fill it with formulas like: =row() then convert those formulas to values Do your sort. Delete the blank rows if they're not at the bottom. sort the remaining data by this helper column delete the helper column. swell estimator wrote: Dave, the sort function was the first thing I tried but ascending and descending compresses the row and removes the original blank rows, but doesn't keep the original order. Pls look at the following and imagine that before I manually removed them, each row had 2 blank rows between them. These are window openings with the width as the first factor and height 2nd factor 1'(w) x 1'(h) . The 2nd # is the constant and the 1st # the variable in multiples of ten and they have to remain in the original order in order to be coherent to someone choosing a window width and height from a list. thanks for help. 1' x 1' - 8' tall wall, 16" o.c. 2' x 1' - 8' tall wall, 16" o.c. 3' x 1' - 8' tall wall, 16" o.c. 4' x 1' - 8' tall wall, 16" o.c. 5' x 1' - 8' tall wall, 16" o.c. 6' x 1' - 8' tall wall, 16" o.c. 7' x 1' - 8' tall wall, 16" o.c. 8' x 1' - 8' tall wall, 16" o.c. 9' x 1' - 8' tall wall, 16" o.c. 10' x 1' - 8' tall wall, 16" o.c. 1' x 2' - 8' tall wall, 16" o.c. 2' x 2' - 8' tall wall, 16" o.c. 3' x 2' - 8' tall wall, 16" o.c. 4' x 2' - 8' tall wall, 16" o.c. 5' x 2' - 8' tall wall, 16" o.c. 6' x 2' - 8' tall wall, 16" o.c. 7' x 2' - 8' tall wall, 16" o.c. 8' x 2' - 8' tall wall, 16" o.c. 9' x 2' - 8' tall wall, 16" o.c. 10' x 2' - 8' tall wall, 16" o.c. 1' x 3' - 8' tall wall, 16" o.c. 2' x 3' - 8' tall wall, 16" o.c..... ~ ~ "swell estimator" wrote: I have a large amount of data that is composed of a text row followed by 2 blank rows. This pattern repeats itself in the entire worksheet. I want to gang remove the blank rows so that I end up with single spaced data. Any help out there. Thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
gang remove blank rows
Ps. Take a look at Fred and Martin's responses.
You may find them easier to do. Dave Peterson wrote: Yep. =row() is the correct formula. Sort your data to get put the empty rows together. then delete those empty rows Sort your data another time by column A. delete column A swell estimator wrote: Dave , the () in =row() is a double paren correct? Here's how I set up a trial of this that I could not get to work. I used a portion of my data and copied it into column A using 208 rows, 1/3 of which was the text formerly listed with 2/3's of the rows blank. I then inserted a new column to the left of my column and that became column A and my original now column B. I selected all 208 cells in column A only, typed =row() in the top cell and hit ctrl,enter and the column filled with #'s from 1 to 208. While column A1-A208was selected I right-clicked on copy and again right clicked on paste special. I then selected both columns A and B to rows 1 - 208, accessed Data and clicked on Sort. The dialog button gave me the choice of column A or B. Column A didn't do anything but selecting column B compressed B1- B208 in the same way as previously when I just hit ascending on the tool bar. bers were not in the correct order. They were in this order: 1' x 1' - 9' tall wall, 24" o.c. 1' x 2' - 9' tall wall, 24" o.c. 1' x 3' - 9' tall wall, 24" o.c. 1' x 4' - 9' tall wall, 24" o.c. 1' x 5' - 9' tall wall, 24" o.c. 1' x 6' - 9' tall wall, 24" o.c. 1' x 7' - 9' tall wall, 24" o.c. 10' x 1' - 9' tall wall, 24" o.c. 10' x 2' - 9' tall wall, 24" o.c. 10' x 3' - 9' tall wall, 24" o.c. 10' x 4' - 9' tall wall, 24" o.c. 10' x 5' - 9' tall wall, 24" o.c. 10' x 6' - 9' tall wall, 24" o.c. 10' x 7' - 9' tall wall, 24" o.c. 2' x 1' - 9' tall wall, 24" o.c. 2' x 2' - 9' tall wall, 24" o.c. 2' x 3' - 9' tall wall, 24" o.c. 2' x 4' - 9' tall wall, 24" o.c. 2' x 5' - 9' tall wall, 24" o.c. 2' x 6' - 9' tall wall, 24" o.c. What did I do wrong? "Dave Peterson" wrote: Insert a new column A Say your last row with data in it is row 777. Select A1:A777 type this: =row() and hit ctrl-enter This will fill that selected range with that same formula. You should see: 1,2,3, ..., 777. With A1:A777 still selected, rightclick on the selected area and choose Copy. Rightclick again and choose Paste Special Choose Values Now those formulas should be values. You can select your range (Say A1:X777) and do your sorting. Then delete the empty rows. Then delete column A. swell estimator wrote: Dave, thank you for replying. I tired everything I could think of using a helper column entering =row() in the first cell of the helper column copying down, tried some other things like puting in the cell reference too but nada. Pls step me thru it in detail. Precisely what do I put in the 1st cell of the helper column and the cells below and what do you mean about converting those formulas to values. thanks, Chris "Dave Peterson" wrote: Add an extra helper column. Fill it with formulas like: =row() then convert those formulas to values Do your sort. Delete the blank rows if they're not at the bottom. sort the remaining data by this helper column delete the helper column. swell estimator wrote: Dave, the sort function was the first thing I tried but ascending and descending compresses the row and removes the original blank rows, but doesn't keep the original order. Pls look at the following and imagine that before I manually removed them, each row had 2 blank rows between them. These are window openings with the width as the first factor and height 2nd factor 1'(w) x 1'(h) . The 2nd # is the constant and the 1st # the variable in multiples of ten and they have to remain in the original order in order to be coherent to someone choosing a window width and height from a list. thanks for help. 1' x 1' - 8' tall wall, 16" o.c. 2' x 1' - 8' tall wall, 16" o.c. 3' x 1' - 8' tall wall, 16" o.c. 4' x 1' - 8' tall wall, 16" o.c. 5' x 1' - 8' tall wall, 16" o.c. 6' x 1' - 8' tall wall, 16" o.c. 7' x 1' - 8' tall wall, 16" o.c. 8' x 1' - 8' tall wall, 16" o.c. 9' x 1' - 8' tall wall, 16" o.c. 10' x 1' - 8' tall wall, 16" o.c. 1' x 2' - 8' tall wall, 16" o.c. 2' x 2' - 8' tall wall, 16" o.c. 3' x 2' - 8' tall wall, 16" o.c. 4' x 2' - 8' tall wall, 16" o.c. 5' x 2' - 8' tall wall, 16" o.c. 6' x 2' - 8' tall wall, 16" o.c. 7' x 2' - 8' tall wall, 16" o.c. 8' x 2' - 8' tall wall, 16" o.c. 9' x 2' - 8' tall wall, 16" o.c. 10' x 2' - 8' tall wall, 16" o.c. 1' x 3' - 8' tall wall, 16" o.c. 2' x 3' - 8' tall wall, 16" o.c..... ~ ~ "swell estimator" wrote: I have a large amount of data that is composed of a text row followed by 2 blank rows. This pattern repeats itself in the entire worksheet. I want to gang remove the blank rows so that I end up with single spaced data. Any help out there. Thank you. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
gang remove blank rows
I want to thank Dave especially for his patience and diligence for staying
with my problem til I was given a solution. And thank you very much Martin - your solution was the simplest and it worked immediately leaving my data in the correct order as I needed. And thank you Fred. Perhaps because I'm using Excel 2003, filtering the data worked this way. After clicking filter on the Data menu, 2 choices: Advanced and Autofilter. Advanced didn't give me options that I could see but Autofilter allowed me to select blanks and non-blanks. I first selected blanks and the whole column went blank. When I right clicked and selected delete or delete row, the whole column collapsed with delete - and nothing happened with delete-row. But by going back in to Autofilter and selecting Non-blanks I achieved the results I wanted - all the items in the order. I assumed the blank rows below were THE blank rows but it didn't seem necessary to delete them. I tried both ways. Could Excel 2003 possibly have a different way of using the Data filter or did I just not know what I was doing? Thank you guys for saving me a ton of tedious work that would have been required to manually remove the blank rows. Chris, the swell estimator "Fred Smith" wrote: Use DataFilter. Select Blanks. Now only blank rows will be displayed. Select these rows, right-click on any row, select Delete Row. Regards, Fred. "swell estimator" wrote in message ... I have a large amount of data that is composed of a text row followed by 2 blank rows. This pattern repeats itself in the entire worksheet. I want to gang remove the blank rows so that I end up with single spaced data. Any help out there. Thank you. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
gang remove blank rows
Hi Chris,
Glad to hear you got it working. With Fred's method you seem to have missed the part where he said "select these rows". After you have applied the Autofilter, select all of the blank rows by their row headers (that selects the entire rows), right click on one of the row headers and select delete, then go back to DataFilterAutofilter and click on it to turn it off. When you have time to play around you may also want to persist with Dave's method to work out how that works. In Excel, as with virtually all programs, there are a lot of different ways of achieving the same result, all useful in different situations. The more you have in your armoury, the better equiped you are whan those tricky little situations arise. Cheers Martin "swell estimator" wrote: I want to thank Dave especially for his patience and diligence for staying with my problem til I was given a solution. And thank you very much Martin - your solution was the simplest and it worked immediately leaving my data in the correct order as I needed. And thank you Fred. Perhaps because I'm using Excel 2003, filtering the data worked this way. After clicking filter on the Data menu, 2 choices: Advanced and Autofilter. Advanced didn't give me options that I could see but Autofilter allowed me to select blanks and non-blanks. I first selected blanks and the whole column went blank. When I right clicked and selected delete or delete row, the whole column collapsed with delete - and nothing happened with delete-row. But by going back in to Autofilter and selecting Non-blanks I achieved the results I wanted - all the items in the order. I assumed the blank rows below were THE blank rows but it didn't seem necessary to delete them. I tried both ways. Could Excel 2003 possibly have a different way of using the Data filter or did I just not know what I was doing? Thank you guys for saving me a ton of tedious work that would have been required to manually remove the blank rows. Chris, the swell estimator "Fred Smith" wrote: Use DataFilter. Select Blanks. Now only blank rows will be displayed. Select these rows, right-click on any row, select Delete Row. Regards, Fred. "swell estimator" wrote in message ... I have a large amount of data that is composed of a text row followed by 2 blank rows. This pattern repeats itself in the entire worksheet. I want to gang remove the blank rows so that I end up with single spaced data. Any help out there. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove Blank and Non Data Rows | Excel Discussion (Misc queries) | |||
HOW CAN I EASILY REMOVE MULTIPLE BLANK ROWS FROM A SPREADSHEET? | Excel Discussion (Misc queries) | |||
formula to remove blank rows | Excel Discussion (Misc queries) | |||
remove or hide blank rows | Excel Discussion (Misc queries) | |||
How do I remove blank rows in Excel? | Excel Discussion (Misc queries) |