Excel 2000: Want to copy value from one row to blank rows 1

[CrystalKim]

I apologize for the vague description, it's the only way I could think of wording it.

I have an Excel spreadsheet. On this spreadsheet is City information, Year and Motor Vehicle Registration Types. The Spreadsheet looks like this. This is sample data

City Year Luxury Cars SUVs Trucks
Agawam 2000 20 50 15
1999 19 48 10
1998 21 44 18
Ware 2000 5 10 2
1999 4 8 1
1998 2 3 2


What I need to do is copy and paste the City information for the other years below them so I can put this in
Access for queries. My desired look would be this

City Year Luxury Cars SUVs Trucks
Agawam 2000 20 50 15
Agawam 1999 19 48 10
Agawam 1998 21 44 18
Ware 2000 5 10 2
Ware 1999 4 8 1
Ware 1998 2 3 2

I know I can copy and paste the info. but I have like 2000 rows of data similar to this and it would probably take me most of the day.

Does anyone know of an easier way to do this.

Thanks in advance

 

[BenRowe]

Assuming first city is column A2 and first available column is F, type the following in F2:
=IF(ISBLANK(a2),INDIRECT("f"&ROW()-1),a2)
Then just copy and paste it down the column.
Then copy column and paste it over column A -- when pasting, Edit, Paste Special, click on values

 

[CrystalKim]

Thanks for the quick reply and the code. My first available city is column A10 and first available column is B10 so I typed in the code you gave me and changed the cell definitions. It copies the first cell correctly, but then after that I get the #Value! error for all remaining rows until I get to my next city.

Here is the code

=IF(ISBLANK(A10),INDIRECT("B"&ROW()-1),A10)

Thanks again for your reply. I was hoping someone would give me code because I need to learn how to use it to make my life easier.

 

[BenRowe]

What do you see in the formula bar when you are in cell B11?

 

[CrystalKim]

This is what is in cell B11.

=IF(ISBLANK(A11),INDIRECT("B10"&ROW()-1),A11)

Thanks

 

[BenRowe]

Delete the 10 in INDIRECT after "B". Make this change in B10 and then copy it down again.

 

[walla07]

Highlight the range you want to fill in (City), press F5 and select the Special box, choose Blanks and press OK. Then type =A10 in the first selected cell (A11) and press Ctrl and Enter, this should copy all the info you need into the blank cells. Then copy the whole column and paste special, values.

 

[CrystalKim]

This is now how the formula looks in B10
=IF(ISBLANK(A10),INDIRECT("b"&ROW()-1),A10)

This is now how the formula looks in B11
=IF(ISBLANK(A11),INDIRECT("b"&ROW()-1),A11)

I am still getting the #Value! Error using both of the methods (BenRowe and walla07).

This is what it now looks like (Real data C&P'd from Excel/Dashes are there only to help seperate the data better).


City-------------TestCity
ABINGTON---------ABINGTON
-----------------#VALUE!
-----------------#VALUE!
-----------------#VALUE!
--------------- #VALUE!
ACTON---------- ACTON
--------------- #VALUE!
--------------- #VALUE!

As you can see the formula recognizes the data only if it is right next to the other column where the original city is. I am stumped and have been playing around with this for a while now.

Thanks

 

[CrystalKim]

When I copied them to a new workbook the formula worked just as you said it would. I have no idea why Excel didn't like the first workbook but took to the second.

Thanks for your help. I have about 5 spreadsheets like this and this is going to save me a lot of frustration!

Have a great weekend.

 

[KenWright]

You could of course always have tried the FAQs first:-

How to fill in the blanks
faq68-4741

Doing it this way allows you to select your entire table and fix it all in one go.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[CrystalKim]

Thanks for the suggestion Ken.

I always seem to forget about them and they are full of great information.

Will try it next time.