Another Excel Question

Factual Questions
1

I was just handed an extremely ugly converted report, and I need to clean this document up. Here’s what we have:



Name           Date       Item
John Smith     1/1/11     apples

               2/1/11     apples

               3/1/11     bananas

Susy Williams  1/1/11     bananas

               5/1/11     pears

               6/1/11     apples

               7/1/11     bricks

Not only do I need to copy the names down to fill in the first cell for each row, I also need to eliminate all the spacing. Help! (Thanks.)

2

OK - I figured out how to delete all the blank rows. Now onto filling in Column A…

3

Use an autofilter to select all the blank lines and delete them.

In column D put this formula =if(A2="",D1,A2)
Copy it to the bottom
Copy the results and paste special as values in column A

4

In cell D1, I put the name from cell A1.
Then, in D2 I put this formula:
=IF(A2="",D1,A2) and dragged it down. This gave me a column with a name in every cell, which can then be copy/ paste special values over into column A. I was able to get it to work with your example.

5

To clarify----this is essentially the same solution, except when I pasted the data into my spreadsheet I didn’t have column headers, so the formula didn’t work in cell D1 since there was no row above it, so I just manually typed that one name in. Or I could have just inserted a row at the top.

6

Select all the rows in column A.

Click Edit | GoTo (or Ctrl-G) and then click Special. Check the Blanks option and click OK.

This should select all of the empty cells in your column.

Now press the equal sign key and the up arrow key, then [Ctrl][Enter].

That should fill in all the blanks.

If you want to have the actual values instead of formulas (like if you’re going to be re-sorting things), select all the rows in column A again and Copy, then Paste Special - Values.