Autofill Down - Prep List for Pivot Table

[MB11]

I have a 4000 row spreadsheet that I want to organize for a pivot table. Some of the rows are missing data, and need to be autofilled with the data above it to be searchable in a pivot table.

<table x:str="" style="border-collapse: collapse; width: 144pt;" width="192" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="3" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" width="64" height="17">Store</td> <td class="xl24" style="border-left: medium none; width: 48pt;" width="64">Style</td> <td class="xl24" style="border-left: medium none; width: 48pt;" width="64">Color</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">A</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">b</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">2</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">B</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">2</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">C</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">D</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">b</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">2</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">b</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">c</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">3</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">E</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">b</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">3</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> </tbody></table>

I want it to look like this, with the blank cells filled with the data above it so that it will be recognized in a pivot table.

<table x:str="" style="border-collapse: collapse; width: 144pt;" width="192" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="3" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" width="64" height="17">Store</td> <td class="xl24" style="border-left: medium none; width: 48pt;" width="64">Style</td> <td class="xl24" style="border-left: medium none; width: 48pt;" width="64">Color</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">A</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">A</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">b</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">A</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">2</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">B</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">B</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">2</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">C</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">D</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">D</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">b</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">D</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">2</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">D</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">2</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">b</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">D</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">2</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">c</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">D</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">3</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">E</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">E</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">b</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="border-top: medium none; height: 12.75pt;" height="17">E</td> <td class="xl24" style="border-top: medium none; border-left: medium none;" x:num="">3</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">a</td> </tr> </tbody></table>

Is there a function or formula that will cover this so I can just run the formula on the entire column and not have to use my cursor and control D on every blank cell?

Thanks!

 

[VoG]

Select the data, press F5, click Special, tick Blanks and click OK.

Press =

Press Up arrow

Hold down CTRL and press Enter.

Copy the data, Edit > Paste Special, tick Values and click OK.

 

[MB11]

Select the data, press F5, click Special, tick Blanks and click OK.

Press =

Press Up arrow

Hold down CTRL and press Enter.

Copy the data, Edit > Paste Special, tick Values and click OK.

Wonderful! Thank you....