Solved

Merge excel tabs with different top rows

Posted on 2013-06-06
6
269 Views
Last Modified: 2013-06-06
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
Comment
Question by:xiss
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 5

Expert Comment

by:magento
ID: 39224999
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39225049
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
 
LVL 1

Author Comment

by:xiss
ID: 39225196
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 39225282
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
 
LVL 1

Author Closing Comment

by:xiss
ID: 39225306
Thanks!!
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39225312
You're welcome.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question