PDA

View Full Version : Solved: reformatting spreadsheet



ssinghal
04-10-2008, 09:23 PM
I have a spreadsheet with raw data for which I would like to reformat so that I can use autofilter to see data in usable form. Here is what I need to do:

I have an empty column A. I need to search each row for the text "Total" in column B. Once found, the NEXT row column B should be copied to column A in that same row. I want that same text to be copied to column A until the next "Total" comes up. Then that total will have same copy as others above it but the next row will start a new copy of the text in row B.

Basically, the Property Names are entered as titles but I need it to be in a cell for each row of data so that i can use autofilter.

I appreciate any help. Thanks.

Sanjeev

mikerickson
04-10-2008, 10:32 PM
In A2 put
=IF(B2="Total",B3,A1)
and Fill Down.

ssinghal
04-11-2008, 04:40 AM
There are 10,000 rows. I need a vba macro to auto do this.

mikerickson
04-11-2008, 05:49 AM
With ThisWorkbook.Sheets("Sheet1")
With Range(.Cells(2,1),.Cells(.Rows.Count,2).End(xlup).Offset(0,-1))
.FormulaR1C1 = "=IF(RC[1]=""Total"",R[1]C[1],R[-1]C)"
.Value = .Value
End With
End With