SQL in VBA EXCEL - compare rows in the same table

Question

I would like to insert into the table OUTPUT the data from the table INPUT. Both, INPUT and OUTPUT have these columns: name, 1H,2H,3H,4H,5H,6H. The data in INPUT have a hierarchy structure. Into the OUTPUT goes from INPUT the last name in hierarchy.

The column in INPUT labeled "goes to OUTPUT?" is not a part of my table .... I added this column only for better understanding, which row goes to OUTPUT

Can you advise the correct SQL command? I am working in excel - vba and LAG, LEAD seems not to be working in EXCEL- vba. I tried UNION (ALL) but it did not work

Answer 1

Try this, where;

• The INPUT table is on sheet INPUT
• The OUTPUT table will be created on sheet OUTPUT
• Code written and tested on Excel 2010 (64 Bit)

Sub Test()
    Dim objADO As Object, strSQL As String, RS As Object
    
    Sheets("OUTPUT").Range("A3:G" & Rows.Count) = ""
    
    Set objADO = CreateObject("AdoDb.Connection")
    
    objADO.Open "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=No'"
          
    strSQL = "Select F1, F2, F3, F4, F5, F6, F7 From [INPUT$A3:H] Where F8= 'YES'"
    
    Set RS = objADO.Execute(strSQL)
    
    Sheets("OUTPUT").Range("A3").CopyFromRecordset RS
End Sub

Answer 2

This is an outline of some VBA code to copy rows which have no items below them in the hierarchy:

Option Explicit

Sub Test()
    Dim firstCellInRow As Range
    Dim lastCellInRow As Range
    Dim firstCellInColumn As Range
    Dim lastCellInColumn As Range
    Dim cell As Range
    Dim endRow As Range
    Dim outRow As Long
    With Sheets("input")
         Sheets("output").Cells(1, 1).Value = "OUTPUT"
         Set firstCellInColumn = .Range("A2")
         Set lastCellInColumn = .Cells(.Rows.Count, 1).End(xlUp)
         outRow = 1
         ' Repeat for each row
         For Each cell In Range(firstCellInColumn, lastCellInColumn)
            Set endRow = cell.Offset(0, 7)
            Set lastCellInRow = endRow.End(xlToLeft)
            ' Copy row if there are no children (cell below and to the right of last cell is empty)
            If lastCellInRow.Offset(1, 1) = "" Then
                outRow = outRow + 1
                Range(cell, lastCellInRow).Copy (Sheets("Output").Cells(outRow, 1))
            End If
        Next cell
    End With
End Sub