+ Reply to Thread
Results 1 to 6 of 6

Extract data from multiple sheets with # match

  1. #1
    Forum Contributor
    Join Date
    08-15-2005
    Location
    Oklahoma
    MS-Off Ver
    2010, 2013
    Posts
    113

    Extract data from multiple sheets with # match

    I want to combine 3 worksheets into 1 by using a common number, i.e. either (1) Entity or (2) API. I have attached a file containing the 3 worksheets I want combined into 1 new sheet called COMBINE. I can record a macro but don't know how to match on a number as provided.

    Thanks

    Jim15
    Attached Files Attached Files
    Jim15

  2. #2
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387
    Jim15,

    On your attached workbook, the data structure on the worksheets "Header", "Well", and "Pod_Abstract" is not the same:

    sheet "Header": columns A thru Y, Entiry is column A, no ABI column
    sheet "Well": columns A thru Q, Entity is column A and ABI is column F
    sheet "Pod_Abstract": columns A thru X, Entity is column A, ABI is column F


    Please attach another workbook with what your data should look like on sheet "COMBINED".


    Have a great day,
    Stan
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259
    Hello Jim15,

    It isn't clear what you want copied. Do you want to copy only the data that matches a particular API? If you want to copy by entity, that would mean copying the data from all 3 sheets, since all the entities are the same. Help us better understand what you want to do.

    Sincerely,
    Leith Ross

  4. #4
    Forum Contributor
    Join Date
    08-15-2005
    Location
    Oklahoma
    MS-Off Ver
    2010, 2013
    Posts
    113

    Updated File

    Sorry - please match all data on Entity number only since API is missing from one sheet.

    The simplest option is to copy all the data from Well and Prod_Abstract and append to the data in Header and matching the lines by Entity. My preferred format is also provided but it is just an edited version of all the data.

    I don't mind editing all the data because that's fast compared to matching lines of several hundred wells. The example provided is small for size.

    Thanks,

    Jim15
    Attached Files Attached Files

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259
    Hello Jim15,

    I have added the macro below to the attached workbook. To run the macro use the ALT+F8 keys to display the Macro list. Select the macro "AppendSheets" and click "OK".
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    08-15-2005
    Location
    Oklahoma
    MS-Off Ver
    2010, 2013
    Posts
    113

    Macro to combine data.

    Thanks for the work; however, I need all data for an entity number on a single row. Each entity number is unique and relates the data together for the Header, Well and Pro_Abstract sheets (see COMBINED_OPTION 1). There is some duplicate information on each sheet but some unique data also.

    Once the macro finished, you would have the same number of rows you started with (13 rows as in Header) but the data from the other 2 sheets would be appended to one of the 13 rows based on Entity.

    Thanks and sorry for the confusion.

    Jim15

+ 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