Solved

Merge excel tabs with different top rows

Posted on 2013-06-06
6
247 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
  • 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now