+ Reply to Thread
Results 1 to 4 of 4

Extracting Data

  1. #1
    tinab
    Guest

    Extracting Data

    Following is an example of the type of data I have in Column A of my
    spreadsheet. This is just one record there are hundreds that are
    similar. I would like to extract the Address and Owner and the 3
    lines below that to another column, worksheet or workbook (it doesn't
    matter). I am new to Excel so I don't know if this is possibe or not.
    I appreciate the help.

    ROLL NUMBER : 002-650-067-44-0000
    JURISDICTION: 200 -
    ADDRESS : 2860- 2858 W First
    OWNER : ABC Company LTD
    2429 MCBAIN AVE
    Washington DC
    123456
    EQUITY: Registered Owner
    LEGAL: PLAN # LOT BLOCK DISTRICT LAND SECTION TOWN RANGE
    LOT DISTRICT SHIP
    MERIDIAN

  2. #2
    Otto Moehrbach
    Guest

    Re: Extracting Data

    Excel is designed to work best with one record per row. In other words, all
    the data you presented would be strung out on one row. Then you would have
    another row with another record.
    You can certainly place your data in the manner in which you have, but
    then you are unable to use many of the neat features/capabilities of Excel.
    I have similar data, one record per row. I also have a button I labeled
    View. All I have to do is click anywhere on any row, click on the View
    button, and that row's data would be re-arranged to the format in which I
    want to see it, and then displayed on the screen. It would then look like
    your data. I can then make any changes/additions I wish, click on the
    Update button, and all that data would be re-arranged into one row for
    storage.
    The above is meant as just tips on how to work with Excel. You might
    think about how you want to arrange your data.
    What you want done will require a looping macro to search for and find each
    record and do the copying you want. The actual code of this macro depends
    on the layout of your data. Is it all in Column A?? If you wish, send me
    what you have and I'll work up something for you. My email address is
    [email protected]. Remove the "nop" from this address. HTH Otto
    "tinab" <[email protected]> wrote in message
    news:[email protected]...
    > Following is an example of the type of data I have in Column A of my
    > spreadsheet. This is just one record there are hundreds that are
    > similar. I would like to extract the Address and Owner and the 3
    > lines below that to another column, worksheet or workbook (it doesn't
    > matter). I am new to Excel so I don't know if this is possibe or not.
    > I appreciate the help.
    >
    > ROLL NUMBER : 002-650-067-44-0000
    > JURISDICTION: 200 -
    > ADDRESS : 2860- 2858 W First
    > OWNER : ABC Company LTD
    > 2429 MCBAIN AVE
    > Washington DC
    > 123456
    > EQUITY: Registered Owner
    > LEGAL: PLAN # LOT BLOCK DISTRICT LAND SECTION TOWN RANGE
    > LOT DISTRICT SHIP
    > MERIDIAN




  3. #3
    Dave Peterson
    Guest

    Re: Extracting Data

    One way using some formulas and filtering.

    Insert 4 new rows (rows 1 to 4)
    Insert a new column A.
    Then in A7 (first row with "Address :" in it:
    =IF(COUNTIF(B4:B7,"address*")>0,"keep","nope")
    drag down

    The select column A and do Data|filter|autofilter
    filter to show the Keep's.

    Copy those visible cells in column B to a new worksheet (cell A1)

    But I like Otto's recommendation to put each value in a separate column.

    If you want...

    Then in B1 of that new worksheet:
    =TRIM(SUBSTITUTE(SUBSTITUTE(A1,"ADDRESS :",""),"OWNER :",""))
    and drag down.
    (this'll clean up the Address/Owner words/leading spaces)

    Then in C1, put this formula.
    =INDEX($B:$B,MOD(COLUMN()-3,4)+1+(ROW()-1)*4)
    Drag across to F1
    and drag down until you start getting 0's.

    Select C:F,
    edit|copy
    Edit|paste special|values

    toss columns A:B.



    tinab wrote:
    >
    > Following is an example of the type of data I have in Column A of my
    > spreadsheet. This is just one record there are hundreds that are
    > similar. I would like to extract the Address and Owner and the 3
    > lines below that to another column, worksheet or workbook (it doesn't
    > matter). I am new to Excel so I don't know if this is possibe or not.
    > I appreciate the help.
    >
    > ROLL NUMBER : 002-650-067-44-0000
    > JURISDICTION: 200 -
    > ADDRESS : 2860- 2858 W First
    > OWNER : ABC Company LTD
    > 2429 MCBAIN AVE
    > Washington DC
    > 123456
    > EQUITY: Registered Owner
    > LEGAL: PLAN # LOT BLOCK DISTRICT LAND SECTION TOWN RANGE
    > LOT DISTRICT SHIP
    > MERIDIAN


    --

    Dave Peterson

  4. #4
    tinab
    Guest

    Re: Extracting Data

    I am trying to move the cells containing the ADDRESS & OWNER data to
    another column or somehow delete all other rows above and below (until
    the next record with the ADDRESS & OWNER data that I need). I have
    done a "find record" and it finds all the cells easily but how can I
    then move them to the next column? This is a text document from a word
    document. Does anyone know if it would be better and if there is a
    way to do this in Word? Thanks for the help!


    [email protected] (tinab) wrote in message news:<[email protected]>...
    > Following is an example of the type of data I have in Column A of my
    > spreadsheet. This is just one record there are hundreds that are
    > similar. I would like to extract the Address and Owner and the 3
    > lines below that to another column, worksheet or workbook (it doesn't
    > matter). I am new to Excel so I don't know if this is possibe or not.
    > I appreciate the help.
    >
    > ROLL NUMBER : 002-650-067-44-0000
    > JURISDICTION: 200 -
    > ADDRESS : 2860- 2858 W First
    > OWNER : ABC Company LTD
    > 2429 MCBAIN AVE
    > Washington DC
    > 123456
    > EQUITY: Registered Owner
    > LEGAL: PLAN # LOT BLOCK DISTRICT LAND SECTION TOWN RANGE
    > LOT DISTRICT SHIP
    > MERIDIAN


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1