External connection in MS Excel

Question

Do you have an idea to combine two tables with an outer join? Know what you need to do in SQL, but now I need Excel.

I have a list of all employees in one column. I have a task table for each employee. I would like to create a function that populates this table with absent employees.

Table 1 - a list of all employees

  Fernando
 Hector
 Vivian
 Ivan

Table 2 - a list of current tasks

  Fernando, task A, 5 hours
 Vivian, task B, 8 hours

Result I would like to achieve

  Fernando, task A, 5 hours
 Vivian, task B, 8 hours
 Hector,, 0 hours
 Ivan,, 0 hours

Thanks so much for any ideas.

Answer 1

Actually there is such a thing as left join in Excel if you use ADO.

Go to the VBA editor (Alt-F11) and add the link (Tools> Links) to the "Microsoft ActiveX Data Objects 2.8 Library". Create a new normal module (Insert> Module) and add this code:

Option Explicit Sub get_employees() Dim cn As ADODB.Connection Set cn = New ADODB.Connection ' This is the Excel 97-2003 connection string. It should also work with ' Excel 2007 onwards worksheets as long as they have less than 65536 ' rows 'With cn ' .Provider = "Microsoft.Jet.OLEDB.4.0" ' .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _ ' "Extended Properties=Excel 8.0;" ' .Open 'End With 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 * FROM [Sheet1$] LEFT JOIN [Sheet2$] ON [Sheet1$].[EMPLOYEE] = " & _ "[Sheet2$].[EMPLOYEE]", 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 

Save the workbook and then run the code and you should get the list on the left on Sheet3. You will see that the Employee column is duplicated, but you can sort it by changing the SELECT clause accordingly. You will also have empty cells, not 0 hours when there is no match

edit: I left the details of the Excel 97-2003 connection string in the code comments, but instead changed the code to use the Excel 2007 connection string. I also added code to display the field names and auto-prepare columns after the output of the recordset

Answer 2

a simple way (maybe the only way?) would be with intermediate cells:

in the result sheet:

  A, B, CD fernando, vlookup(...), vlookup(...), =if(ISNA(B2),"<default-1>"), =if(ISNA(B2),"deafult2) 

then hide cols C and B

Edit:

In fact, there is something close: a pivot table . You can organize the data so that non-referencing cells remain empty.

But another solution, different from the formulas - it may not correspond, depends on your use.

Answer 3

This method allows you to copy and paste, filter and sort to perform an external connection in Excel and is useful for only one. The idea is to use VLOOKUP to find all matching records to the left and right of the tables to the right and left. [Adding another entry to table 2 to display the outer join]

Table 1

 Fernando Hector Vivian Ivan 

table 2

 Fernando, task A, 5 hours Vivian, task B, 8 hours Thomas, task A, 5 hours 

Copy both tables into one table, where the first left columns will be indicated in table 1, and the first right columns and last rows will occupy the first rows and table 2 (the headers should be row 1 for both tables). Create a VLOOKUP function for the following two columns to find the corresponding keys from the tables from left to right and from right to left.

Table 3

 Name Name Task Hours Match 1 Match 2 Fernando =VLOOKUP(A2,B:B,1,FALSE) =VLOOKUP(B2,A:A,1,FALSE) Hector =VLOOKUP(A3,B:B,1,FALSE) =VLOOKUP(B3,A:A,1,FALSE) Vivian =VLOOKUP(A4,B:B,1,FALSE) =VLOOKUP(B4,A:A,1,FALSE) Ivan =VLOOKUP(A5,B:B,1,FALSE) =VLOOKUP(B5,A:A,1,FALSE) Fernando task A 5 hours =VLOOKUP(A6,B:B,1,FALSE) =VLOOKUP(B6,A:A,1,FALSE) Vivian task B 8 hours =VLOOKUP(A7,B:B,1,FALSE) =VLOOKUP(B7,A:A,1,FALSE) Thomas task B 8 hours =VLOOKUP(A8,B:B,1,FALSE) =VLOOKUP(B8,A:A,1,FALSE) 

Table 3 Result

 Name Name Task Hours Match 1 Match 2 Fernando Fernando N/A Hector N/AN/A Vivian Vivian N/A Ivan N/AN/A Fernando task A 5 hours N/A Fernando Vivian task B 8 hours N/A Vivian Thomas task B 8 hours N/AN/A 

NOTE For large datasets, the next step will take a very long time due to the calculation of VLOOKUP. Copy and paste the columns to match 1 and match 2 columns as values ​​so that VLOOKUP does not recount during filtering.

Filter by match 1 and match 2 to see only N / A results. Copy the master data to another heading sheet.

 Name Name Task Hours Match 1 Match 2 Hector N/AN/A Ivan N/AN/A Thomas task B 8 hours N/AN/A 

Filter by match 1 and match 2 to not see N / A results. Sort the keys for both so that when copying and pasting everything matches. Copy and paste the data in table 1 into a new sheet below the previously inserted data. Then copy and paste the data from table 2 to the right of the data from table 1. that you just pasted.

 Name Name Task Hours Match 1 Match 2 Fernando Fernando N/A Vivian Vivian N/A Fernando task A 5 hours N/A Fernando Vivian task B 8 hours N/A Vivian 

The result is lower, and you can delete, sort, regardless of external related data.

 Name Name Task Hours Hector Ivan Thomas task B 8 hours Fernando Fernando task A 5 hours Vivian Vivian task B 8 hours