extracting some data

  • 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!!!

  • Re: extracting some data


    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.


  • Re: extracting some data

    Try this, adjusting the negative row offset...

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • Re: extracting some data

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!