Copy current cell to empty cells above, open to better suggestions!

[NerdNinja]

I'm working on cleaning up a report that contains statistics for all counties in the U.S. by state. I'm trying to find a way to filter the data so I can select only the state and counties I need to look up. Currently, the report contains each county on a row, with the only separation between states being a subtotal row containing the text "Total for Alabama/Alaska/etc". This text appears in column B. I've got a formula that can extract the state name and insert into the cell to the left (column A). But that means I have to copy that cell value (for example copy "Alabama" from cell A406) and paste to all empty cells above. Then go to the next subtotal, extract that state name (for example copy "Alaska" from cell A450) and copy to all empty cells above....stopping just short of where Alabama's data is. There has GOT to be an easier way to do this, I just haven't worked it out yet. I'd appreciate any suggestions to make this process a lot faster. Thanks!

 

[Fishboy]

I'm working on cleaning up a report that contains statistics for all counties in the U.S. by state. I'm trying to find a way to filter the data so I can select only the state and counties I need to look up. Currently, the report contains each county on a row, with the only separation between states being a subtotal row containing the text "Total for Alabama/Alaska/etc". This text appears in column B. I've got a formula that can extract the state name and insert into the cell to the left (column A). But that means I have to copy that cell value (for example copy "Alabama" from cell A406) and paste to all empty cells above. Then go to the next subtotal, extract that state name (for example copy "Alaska" from cell A450) and copy to all empty cells above....stopping just short of where Alabama's data is. There has GOT to be an easier way to do this, I just haven't worked it out yet. I'd appreciate any suggestions to make this process a lot faster. Thanks!

Hi NerdNinja,

Can you share a visual example of the data please? That can either be a screenshot or by googling the MrExcel HTML Maker and using that to share data directly from your workbook onto the forum. Once we can confirm exactly what we are supposed to be seeing as the "before" state of the data, we will stand a better chance of helping you find your solution.

Also, out of interest, are you looking for a formula only solution, or are VBA macros an option?