What you are looking to do is a LEFT OUTER JOIN on the table in the second sheet to the table in the first.
So below an Option 1 with PowerQuery and 2 with VBA-SQL
Option 1:
You can do this with the free add-in PowerQuery (in built in 2016).
1) Set your data up in sheet 1 and 2 as tables (click in a populated cell within the data range and then press Alt + T > select my table has headers.
2) Highlight each table and then go to data tab (2016) or Powerquery tab (2010-2013) and create new query data from table (Get and Transform)
This will pop up a query editor window showing your table (the query/table you can rename on the right hand side)
3) You can then select close and load to > only create connection (from top left hand corner of window
Choosing connection only
Repeat for tables in sheet 1 and 2.
4) Then create new query > combine queries > merge
Make sure your sub tasks table is the first table selected and Main tasks is second. Click on Content_Category_Product Sub Type columns in both tables so they are highlighted (this will be the join column)
Check that join kind is Left outer and that there is a green tick for selection matching.
Then click ok and load to sheet3.
5) Sort the resulting table on first column ascending
6) View result :
There are plenty of resources about Powerquery you can look up. This will allow you to change column names etc. You can also delete unwanted columns to match image you posted.
Or Option 2:
With SQL, adapting an approach from barrowc, and a function by Johan Kreszner, making sure you go to the VBA editor (Alt-F11) and add a reference (Tools > References) to "Microsoft ActiveX Data Objects X.X Library".
I have assumed only the tables are in sheet 1 and 2 but otherwise you may need to alter the SQL to target the ranges of the tables (List Objects) which is why i have included Johan's function to return the range of a list object provided you pass the table name as a string.
Option Explicit
Sub LeftJoinTables()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 12.0 Macro;IMEX=1;HDR=YES"";"
.Open
End With
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT [Main Task], [Sub Task], [Budget Hours] FROM [Sheet2$] LEFT JOIN [Sheet1$] ON [Sheet2$].[Content Category_Product Sub type] = " & _
"[Sheet1$].[Content Category_Product Sub type] ORDER BY [Main Task]", cn
Dim fld As ADODB.Field
Dim i As Integer
With ThisWorkbook.Worksheets("Sheet3")
.UsedRange.ClearContents
i = 0
For Each fld In rs.Fields
i = i + 1
.Cells(1, i).Value = fld.Name
Next fld
.Cells(2, 1).CopyFromRecordset rs
.UsedRange.Columns.AutoFit
End With
rs.Close
cn.Close
End Sub
Public Function GetRange(ByVal sListName As String) As String
Dim oListObject As ListObject
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
For Each ws In wb.Sheets
For Each oListObject In ws.ListObjects
If oListObject.Name = sListName Then
GetRange = "[" & ws.Name & "$" & Replace(oListObject.Range.Address, "$", "") & "]"
Exit Function
End If
Next oListObject
Next ws
End Function
