• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Merge excel tabs with different top rows

Hello all,

I have searched expert exchange for this and tried different macro's but no luck..
I have one excel sheet with three tabs. the first two tabs have the following information;

TAB 1: Accounts
Firstname      Lastname      Email      Language      NMUA      Status
John      Doe      john.doe@example.com      en-us      nmua000001      Installed
Jane      Doe      jane.doe@example.com      en-us      nmua000005      Installed

TAB 2: Drives
Email       Label       Name       Type       Node       Status       Size
John.doe@example.com      DriveA       nmsa000002       Team        drive02.example.com       Installed       2080M
John.doe@example.com      DriveC       nmsa000003       Team        drive02.example.com       Installed       1024M
jane.doe@example.com      DriveB       nmsa000006       Personal        drive01.example.com       Installed       1M

TAB 3: Total (Empty)

In the last tab I want the data from both tabs combined, for example;

NMUA      Firstname      Lastname      Email      Label      Name      Type      Node      Size      Language      Status
nmua000001      John      Doe      john.doe@example.com      DriveA      nmsa000002      Team       drive02.example.com       2080M      en-us      Installed
nmua000001      John      Doe      john.doe@example.com      DriveC      nmsa000003      Team       drive02.example.com       1024M      en-us      Installed
nmua000005      Jane      Doe      jane.doe@example.com      DriveB      nmsa000006       Personal        drive01.example.com       1M      en-us      Installed

Is this possible?

Regards,

kasper
Consolidated.xlsx
0
xiss
Asked:
xiss
  • 3
  • 2
1 Solution
 
magentoCommented:
Hi Kasper,

You can use excel formula Vlookup to get the required data from sheet1 to sheet2. (Sheet 2 has all the columns )

But it involve some manual work . Lets hope for someone write a macro for you.

BR,

Magento
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

The Visual Basic for Applications code within the code module for the [Total] worksheet within the attached workbook is as follows:

Option Explicit
Private Sub Worksheet_Activate()

' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28149312.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               28149312
' Question Title:   Merge excel tabs with different top rows
' Question Asker:   xiss                                      [ http://www.experts-exchange.com/M_4304080.html ]
' Question Dated:   2013-06-06 at 09:40:16
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ http://NigelLee.info ]
' --------------------------------------------------------------------------------------------------------------

  Dim blnApplication_ScreenUpdating                     As Boolean
  Dim lngErr_Number                                     As Long
  Dim objADODB_Connection                               As Object
  Dim objADODB_Recordset                                As Object
  Dim strSQL                                            As String
  Dim strErr_Description                                As String
 
  On Error GoTo Err_Workbook_SheetActivate
 
  blnApplication_ScreenUpdating = Application.ScreenUpdating
 
  Application.ScreenUpdating = False
  
  Set objADODB_Connection = CreateObject("ADODB.Connection")
 
  objADODB_Connection.Provider = "Microsoft." & IIf(Val(Application.Version) <= 11#, "Jet.OLEDB.4.0", "ACE.OLEDB.12.0")
  objADODB_Connection.ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=Excel " & _
                                          IIf(Val(Application.Version) <= 11#, "8", "12") & ".0;"
  objADODB_Connection.Open
 
  strSQL = ""
 
  strSQL = strSQL & "SELECT "
  strSQL = strSQL & "[Accounts].[NMUA],"
  strSQL = strSQL & "[Accounts].[Firstname],"
  strSQL = strSQL & "[Accounts].[Lastname],"
  strSQL = strSQL & "[Accounts].[Email],"
  strSQL = strSQL & "[Drives].[Label],"
  strSQL = strSQL & "[Drives].[Name],"
  strSQL = strSQL & "[Drives].[Type],"
  strSQL = strSQL & "[Drives].[Node],"
  strSQL = strSQL & "[Drives].[Size],"
  strSQL = strSQL & "[Accounts].[Language],"
  strSQL = strSQL & "[Accounts].[Status] "
       
  strSQL = strSQL & "FROM "
  strSQL = strSQL & "[EXCEL " & _
                     IIf(Val(Application.Version) <= 11#, "8", "12") & ".0;"                                                                            ' Note: Val(...) only recognizes a period ["."] as a valid decimal separator
  strSQL = strSQL & "IMEX=1;"
  strSQL = strSQL & "HDR=Yes;"
  strSQL = strSQL & "DATABASE=" & ActiveWorkbook.FullName & "].[Drives$]        As [Drives] "
      
  strSQL = strSQL & "INNER JOIN "
  strSQL = strSQL & "[EXCEL " & _
                     IIf(Val(Application.Version) <= 11#, "8", "12") & ".0;"                                                                            ' Note: Val(...) only recognizes a period ["."] as a valid decimal separator
  strSQL = strSQL & "IMEX=1;"
  strSQL = strSQL & "HDR=Yes;"
  strSQL = strSQL & "DATABASE=" & ActiveWorkbook.FullName & "].[Accounts$]      As [Accounts] "
  strSQL = strSQL & "ON "
  strSQL = strSQL & "UCase$([Drives].[Email])=UCase$([Accounts].[Email])"
      
  Set objADODB_Recordset = CreateObject("ADODB.Recordset")

  objADODB_Recordset.CursorType = 3                                                                                                                      ' adOpenStatic
  objADODB_Recordset.CursorLocation = 3                                                                                                                  ' adUseClient
  objADODB_Recordset.ActiveConnection = objADODB_Connection

  objADODB_Recordset.Open (strSQL)

  Cells.ClearContents
  
  Range("A1:K1") = Array("NMUA", _
                         "Firstname", _
                         "Lastname", _
                         "Email", _
                         "Label", _
                         "Name", _
                         "Type", _
                         "Node", _
                         "Size", _
                         "Language", _
                         "Status")

  [A2].CopyFromRecordset objADODB_Recordset
  [A1].Select
  
Exit_Workbook_SheetActivate:

  On Error Resume Next
 
  If Not (objADODB_Recordset Is Nothing) Then
     objADODB_Recordset.Close
     Set objADODB_Recordset = Nothing
  End If
 
  If Not (objADODB_Connection Is Nothing) Then
     objADODB_Connection.Close
     Set objADODB_Connection = Nothing
  End If

  Application.ScreenUpdating = blnApplication_ScreenUpdating
  
  Exit Sub
 
Err_Workbook_SheetActivate:

  lngErr_Number = Err.Number
  strErr_Description = Err.Description
  
  On Error Resume Next
  
  Application.ScreenUpdating = True
  
  MsgBox "Error #" & CStr(lngErr_Number) & _
          vbCrLf & vbLf & _
          strErr_Description, _
          vbExclamation Or vbOKOnly, _
          ActiveWorkbook.Name
         
  Resume Exit_Workbook_SheetActivate
 
End Sub

Open in new window


Please download/save this workbook locally (instead of simply opening the file from your web browser's cache).

If you wish to make changes to the contents of the [Accounts] worksheet &/or [Drives] worksheet please do so.

Selecting (activating) the [Total] worksheet will collate the data from those two worksheets & present grouped totals as you required.

PS. It should not be necessary to save the workbook before viewing the [Total] worksheet, but if you find the totals are not correct, I would recommend saving the workbook in advance of doing this.

BFN,

fp.
Q-28149312.xls
0
 
xissAuthor Commented:
That is so awsome thanks! Is it also possible to add an extra tab (AD) with the following layout;

UserName       EMailAddress       distinguishedName       company
Jane.Doe      jane.doe@example.com       CN=Jane Doe      OU=CompanyA
John.Doe      john.doe@example.com       CN=John Doe      OU=CompanyB
Support.User      support@example.com       CN=Support User      OU=CompanyC

And if the e-mail exists in that tab the Total tab displays in the row AD: "yes" and if not it displays "no".

I have attached an example. Thanks so much for the help!!

Regards,

Kasper
Consolidated-NEW.xlsx
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
[ fanpages ]IT Services ConsultantCommented:
And if the e-mail exists in that tab the Total tab displays in the row AD: "yes" and if not it displays "no".

I presumed you meant column, not row.

Please find an update workbook attached.
Q-28149312b.xls
0
 
xissAuthor Commented:
Thanks!!
0
 
[ fanpages ]IT Services ConsultantCommented:
You're welcome.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now