Extract data from multiple sheets with # match

**Jim15** · 11-07-2008, 03:41 PM

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

**stanleydgromjr** · 11-07-2008, 04:45 PM

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

**Leith Ross** · 11-07-2008, 04:50 PM

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

**Jim15** · 11-07-2008, 05:23 PM

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

**Leith Ross** · 11-07-2008, 05:54 PM

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".

Sub AppendSheets()

  Dim LastCol As Long
  Dim LastRow As Long
  Dim DstWks As Worksheet
  Dim R As Long
  Dim Rng As Range
  Dim SrcWks As Worksheet
  
  
    Set DstWks = Worksheets("Header with ALL DATA_OPTION 2")
    R = DstWks.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
      R = 2
      For Each SrcWks In Worksheets
        If SrcWks.Name <> DstWks.Name Then
          With SrcWks
            LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            LastRow = IIf(LastRow < 2, 2, LastRow)
            Set Rng = .Range(.Cells(2, "A"), .Cells(LastRow, LastCol))
          End With
          Rng.Copy Destination:=DstWks.Cells(R, "A")
          R = R + LastRow - 1
        End If
      Next SrcWks
      
End Sub

Sincerely,
Leith Ross

**Jim15** · 11-07-2008, 07:16 PM

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