IS there a “compress” feature in excel? What I mean is If I have a documnt that has 3000 rows, but only with data on every 3rd row, is there a function I can run to make it a 1000 row document with no blank lines?
Yes there is, and I’m no Guru.
Just use the AutoFilter. Select all of your columns and then select Data -> Filter -> AutoFilter. You will see a little drop-down menu arrow in each collumn header. Go to the collumn with the black cells and choose “non-blanks” from the drop-down.
Tada!
thanks, Sdimbert.
one gripe with this method…it hoses the row #'s on the side.
So, in my case of:
1
2
3 xxxx
4
5
6 xxxx
your suggesstion yields:
3 xxxx
6 xxxx
when what I want is:
1 xxxx
2 xxxx
anyone know how to do this?
Not that I know of.
Select your column [click the A, B, etc] at the top. [If you have several columns, click the leftmost box on the header line to select the spreadsheet.]
On the menu bar, go to Data, Sort. Have it sort by the column you want. All the rows with blanks in that column will be moved together.
If you need to keep your data in the original order, do this FIRST. Create a new column. fill it with sequential numbers [1,2,3, etc, Excell can generate them, but you have to mark the range. Hint, hide all but one column to reduce the time spent redrawing the screen as you scroll.]
Now sort as above. Then mark the portion of the spreadsheet with “good” rows. Now sort again by your sequential column.
OR do the auto-filter function, then cut and paste the data into a new sheet. THe row numbers will be sequential.
starfish, I would do this, except Excel can’t effectively sort my column.
I have number headings in each of format
1.1.1.1.1
1.1.1.2
1.1.1.2.1
1.1.2.1.1
And excel can’t sort those sequentially.
That’s it, Mr. Z. That’ll work.
thanks to all who contributed.
Just one note, 'cause it’s cool…
Enter a 1 in the first row and a 2 directly beneath it. Then, select both cells. See the little “grab-sqare” in the bottom right-hand corner of the selection? Grab it and drag straight down… Excell will fill in 3,4,5,6…!
It works for days of the week, months of the year, even a list of names you input. Excell can identify a pattern and repeat it. Cool cool
cool!
Do it using my second method. Make a new column with sequencial numbers. Select the spreadsheet. Sort by your column. You don’t care what order the rows come out in. All the blank values in the column being sorted will be grouped. Delete them, or just copy the rows you want to keep - including the value in the new sequential column. Now sort these by the sequntial column and your rows end up back in their original order. You can then delete the sequential column.
I tried sorting numbers like
1.1.1
1.1.2
1.2.1
1.1.2.1
and they came out correct
1.1.1
1.1.2
1.1.2.1
1.2.1
Starfish,
I stand corrected.
I could have sworn that I tried that last tiem I was faced with the same problem, and it didnt work.
You were right, thanks for the info.
That movie taught me some important lessons in life. 1. I can build a robot that loves me. 2. I can reanimate my dead girlfriend by jamming bits of metal and silicon into her skull. Both are lessons I use on a daily basis…