[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Merge excel tabs with different top rows

Posted on 2013-06-06
6
Medium Priority
?
281 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

656 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