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

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

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

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