Easy way to shift rows of cells forward [SOLVED]

[Mizuno341]

Hey,


Ive been looking at some vintage data so its shaped kind of like an upside down pyramid... (0 are empty cells)

[pre]

300  290  250  200  100.....
0    305  300  290  230.....
0    0    310  300  285.....
0    0    0    325  320.....
0    0    0    0    350.....for several rows

Whats the easiest way to get them to read like this below the orignal data without going through and deleting empty cells or dragging the formulas forward,

300  290  250  200  100.....
305  300  290  230.....
310  300  285.....
325  320.....
350.....

[/pre]
Thanks!

 

[Luke M]

This method removes unwanted cells, so if you want to preserce original data, copy to a new location/sheet. Would be best to then select all cells of interest before starting with step 1.

1. Ctrl+f to bring up Find dialogue

2. Find what: 0

3. Set options to Match entire cell contents, Lookin Values.

4. hit Find all

5. Within the results, click one of the results, then hit Ctrl+A to select all search results.

6. Close Find dialogue

7. Hit Ctrl + - (minus key)

8. On the Delete dialogue, choose 'Shift cells left'

9. Done

 

[Mizuno341]

I don't actually have 0's in the cell, they are empty cells, the formatting in this forum wouldn't allow spaces so i needed to put them in there to denote them.

 

[Luke M]

Oops, sorry about that, I missed that in your first post. In that case, select the range of cells, hit Ctrl+g (goto dialogue), then click special cells, blank cells, ok out. Then start at step 7 of above.

 

[Mizuno341]

I didnt think this would matter but apparently it does, the copy/paste value version i tried this on did not recognize the cells as blank (even though there is nothing in there) because the original had a if statement that if it was zero to show "". This may be hopeless, lol.

 

[Luke M]

One last trick. Go back into the Special cells section, but this time select Constants (or formulas, if they are still formulas), and uncheck the box for Numbers. This should nab the little buggers!

 

[Mizuno341]

WOO HOO! Thanks! It worked!