extracting some data

jjst34 · Mar 23rd 2005

Hi,, I have a large spreadsheet, that I'm looking to extract some info from, into another sheet in the same workbook... Basically waht I want to do is move info from sheet 'sec8' to 'parsed'.
I would like it to lookup in column B to find "TOTAL FOR HOUSING AUTH:"
Extract that plus 2 digits which are always in the same place, so some type of offset, if I do a Ctrl + Up, then down 2 rows, right one column, and the first 2 digits in that cell.
Then also extract the info in the next 3 columns C-E, which are dollar values next to the "TOTAL FOR HOUSING AUTH:"

In the end I want the new sheet 'parsed' to be like this
-----------columnA B- C D
TOTAL FOR HOUSING AUTH: 02 $xxx $xxx $xxx
TOTAL FOR HOUSING AUTH: 09 $xxx $xxx $xxx

Any help is very much appreciated!!!

**Krishnakumar** · Mar 24th 2005

Re: extracting some data

Hi,

Try this array formula in A2. Drag it right and down the column.

=IF(ROW()-1>COUNTIF(sec8!$B$2:$B$25,"total for housing auth:"),"",INDEX(sec8!B$1:B$25,SMALL(IF(sec8!$B$2:$B$25="total for housing auth:",ROW(sec8!$B$2:$B$25)),ROW()-1)))

I didn't get you fully the 'extract 2 digit' part. Need more information or attach a sample workbook.

HTH

dangelor · Mar 24th 2005

Re: extracting some data

Try this, adjusting the negative row offset...

Code

Sub extracts()
    Dim f As Range, db As Range
    Dim firstAddress As String, c As String
    
    With Sheets("Sec8")
        Set db = Intersect(.Range("b:b"), .UsedRange)
    End With
    c = "TOTAL FOR HOUSING AUTH:"
    With db
        Set f = .Find(c, LookIn:=xlValues)
        If Not f Is Nothing Then
            firstAddress = f.Address
            Do
                f.Copy Sheets("parsed").Range("a65536").End(xlUp).Offset(1)
                f.Offset(-20, 1).Copy Sheets("parsed").Range("b65536").End(xlUp).Offset(1)
                f.Offset(0, 1).Copy Sheets("parsed").Range("c65536").End(xlUp).Offset(1)
                f.Offset(0, 2).Copy Sheets("parsed").Range("d65536").End(xlUp).Offset(1)
                f.Offset(0, 3).Copy Sheets("parsed").Range("e65536").End(xlUp).Offset(1)
                Set f = .FindNext(f)
            Loop While Not f Is Nothing And f.Address <> firstAddress
        End If
    End With
End Sub

Display More

jjst34 · Mar 24th 2005

Re: extracting some data

With a few minor adjustments to your code Dangelor, it works like a charm... THanks a lot for your help!

extracting some data

Participate now!

Share