# Another Excel Question

**URL:** <https://boards.straightdope.com/t/another-excel-question/626578>\
**Category:** Factual Questions\
**Created:** [June 28, 2012, 2:03pm UTC](https://boards.straightdope.com/t/another-excel-question/626578 "2012-06-28T14:03:24Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\
**Post date:** [June 28, 2012, 2:03pm UTC](https://boards.straightdope.com/t/another-excel-question/626578/1 "2012-06-28T14:03:24Z")

</div>

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

```auto

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.)

---

<div class="post-metadata">

**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\
**Post date:** [June 28, 2012, 2:15pm UTC](https://boards.straightdope.com/t/another-excel-question/626578/2 "2012-06-28T14:15:50Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\
**Post date:** [June 28, 2012, 2:19pm UTC](https://boards.straightdope.com/t/another-excel-question/626578/3 "2012-06-28T14:19:28Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\
**Post date:** [June 28, 2012, 2:21pm UTC](https://boards.straightdope.com/t/another-excel-question/626578/4 "2012-06-28T14:21:36Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![batsto](https://avatars.discourse-cdn.com/v4/letter/b/b2d939/32.png) [@batsto](https://boards.straightdope.com/u/batsto)\
**Post date:** [June 28, 2012, 2:25pm UTC](https://boards.straightdope.com/t/another-excel-question/626578/5 "2012-06-28T14:25:06Z")

</div>

> [@don\_t\_ask](#):
>
> 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

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.

---

<div class="post-metadata">

**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\
**Post date:** [June 28, 2012, 2:34pm UTC](https://boards.straightdope.com/t/another-excel-question/626578/6 "2012-06-28T14:34:58Z")

</div>

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.