CODEWITHSUNDEEP
excelvba
user8211934
user8211934

Reputation:

How to merge two tables from two different work sheets into one final table with different columns?

I am very new to coding (a newbie).

At work place, I have following to do:

A) I have Table A B) I have Table B C) I need output Table C (how do I get it?)

I am describing in details here:

Input Tables A and B:

enter image description here

Output Table C:

enter image description here

I have to get Output table C for many many files and thus will be very difficult to match up Order and Order-1 in the Tables using copy and past option in excel.

Thanks a ton for looking into this.

Apologies if the question is not clear.

Please let me know if you need any further information regarding this.

Upvotes: 0

Views: 170

Answers (2)

Dy.Lee
Dy.Lee

Reputation: 7567

this is Vba of SQL. practice sub myQuery.

Dim Ws As Worksheet
Dim strSQL As String

Sub myQuery()

Set Ws = Sheets("C")

    strSQL = "SELECT Time, Type, User, '' as [Order], [Order-1], Urea"
    strSQL = strSQL & " FROM [A$] where not isnull(Time) "
    strSQL = strSQL & " Union All  "
    strSQL = strSQL & "SELECT Time, '', User, [Order], [Order-1], Urea "
    strSQL = strSQL & "FROM [B$] where not isnull(time) "
    strSQL = strSQL & "ORDER BY Time "

    DoSQL

End Sub
Sub DoSQL()

    Dim Rs As Object
    Dim strConn As String
    Dim i As Integer

    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & ThisWorkbook.FullName & ";" & _
            "Extended Properties=Excel 12.0;"


    Set Rs = CreateObject("ADODB.Recordset")
    Rs.Open strSQL, strConn

    If Not Rs.EOF Then
         With Ws
            .Range("a1").CurrentRegion.Clear
            For i = 0 To Rs.Fields.Count - 1
               .Cells(1, i + 1).Value = Rs.Fields(i).Name
            Next
            .Range("a" & 2).CopyFromRecordset Rs
            .Columns(1).NumberFormatLocal = "[$-409]mm/dd/yy h:mm AM/PM;@"
        End With
    End If
    Rs.Close
    Set Rs = Nothing
End Sub

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

You can try taking a UNION of the two tables:

SELECT Time, Type, User,    '', Order-1, Urea
FROM TableA
UNION ALL
SELECT Time,   '', User, Order, Order-1, Urea
FROM TableB
ORDER BY Time

If you're not really using MySQL, then you should not have tagged your question as such, which generated an answer like this one.

Upvotes: 1

Related Questions