How do I merge the data from multiple worksheets into a single worksheet?

Posted on 2011-10-27
Last Modified: 2012-05-12
I have a spreadsheet which contains multiple worksheets, 2 of which contain data of an identical structure. Each of the 2 worksheets contain ZIP CODE, STATE, CITY, and a series of 3 business related indicators (FLAG A, B, and C).

Worksheet A contains static data for the approximate 40,000 zip codes that are outside of our business zone.

Worksheet B contains the same data for the approximate 4,000 zip codes that are within our business zone. The cells for this sheet are dynamically linked to a separate spreadsheet which serves as a sort of central data repository.

I can't store all 44,000 rows in the data repository, as the update of a 1/4 million cells causes the update to fail via resources.

I need to sort and merge the data from worksheets A and B into worksheet C, so that I have a single data source for performing array functions (specifically a VLookup) from a census table, which can contain ZIPs from either of the 2 worksheets.
Question by:FlyingElvii
    LVL 18

    Expert Comment

    by:Richard Daneke
    You can try to create, in a blank workbook, a pivot table that uses both worksheets from multiple ranges.  Use State for a row source and count of Zip for values to place some data into the workbook.   Double click on the grand total and Excel will output a new worksheet with all of the records.  

    Your limits in 2002 are 65535 for rows and, in 2007 or 2010, over 1,048,000.  Trial versions are availabe directly from the Microsoft web pages.  But, there are many reasons not to upgrade.

    Author Comment

    Hi. Thanks for the response.

    This looks like it's on the right track, but I need the syntax for including the 2 different worksheet ranges as my input for the wizard's 'step 2' (input for the pivot table).

    My ranges are: 'All ZipsNonBusiness'!$A$1:$G$39509
                    and: 'All ZipsBusiness'!$A$1:$G$4741

    Thanks for your help.

    Author Comment

    Hi again.

    I figured out how to use the multiple input sources, however I receive the following error:

    "A field in your source data has more unique items than can be used in a PivotTable report. MicroSoft Excel may not be able to create the report, or may create the report without the data from this field."

    No report was created.
    LVL 26

    Accepted Solution


    If you're still interested in your original approach, please see the code below. A few notes...
    (1) Set the XHEADER_ROWS constant to the no. of rows in the header (so it isn't duplicated from the second sheet). You can set this to 0 if you want all rows from both sheets.
    (2) The macro will handle more than two sheets, just insert them into the line starting "For Each xOldSheet".
    (3) The macro unprotects, unfilters, unhides rows and columns the two sheets.
    (4) Because formulas may cause problems in the new "Merge" sheet, the macro pastes values only. Obviously it can be changed if you want formatting also or formulas instead.
    (5) The macro doesn't bother checking if "Merge" already exists - instead it fails ungracefully.
    (6) The results are slightly untidy if the second sheet only has the header rows (i.e. no data).
    Option Explicit
    Const XHEADER_ROWS = 1
    Sub Merge_Two_Sheets_Paste_Values()
    Dim xNewSheet As Worksheet
    Dim xOldSheet As Worksheet
    Dim xSheet As Worksheet
    Dim xResponse As Long
    Dim xhold As String
    Dim xLast_Row As Long
    Dim xStart_Row As Long
    Dim xFirst As Boolean
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlManual
    End With
        Set xNewSheet = Sheets.Add
        xNewSheet.Name = "Merge"
        xFirst = True
        For Each xOldSheet In ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2"))
            If xOldSheet.ProtectContents Then xOldSheet.Unprotect ' If there's a password thrn add it as a string here.
            If xOldSheet.FilterMode Then xOldSheet.ShowAllData
            xOldSheet.Cells.EntireColumn.Hidden = False
            xOldSheet.Cells.EntireRow.Hidden = False
            xLast_Row = Sheets("Merge").Range("A1").SpecialCells(xlLastCell).ROW
            If xFirst Then
                xStart_Row = 1
                xFirst = False
                '''If xLast_Row = XHEADER_ROWS Then Exit For
                xStart_Row = XHEADER_ROWS + 1
                xLast_Row = xLast_Row + 1
            End If
            xOldSheet.Range("A" & xStart_Row & ":" & xOldSheet.Range("A1").SpecialCells(xlLastCell).Address).Copy
            Sheets("Merge").Cells(xLast_Row, 1).PasteSpecial (xlPasteValues)
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .Calculation = xlAutomatic
    End With
    End Sub

    Open in new window


    Author Closing Comment

    This solution resolved my issue. Additionally, the code is re-usable for addressing any similar updates. It was very well-explained and easy to follow and understand.
    Thanks Brian!
    LVL 26

    Expert Comment

    Many thanks, FlyingElvii!

    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

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    734 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

    23 Experts available now in Live!

    Get 1:1 Help Now