+ Reply to Thread
Results 1 to 6 of 6

Consolidate two data sets

  1. #1
    Registered User
    Join Date
    01-23-2014
    Location
    Bridges misssse
    MS-Off Ver
    Excel 2007
    Posts
    6

    Consolidate two data sets

    Hi all, how do you consolidate two data sets to compile one summary table which adds together the number for each product in each month. The consolidated table would contain the same column headings with the added number in column three. Is there a way of automating this process (Pivot?Consolidate?). Thank you

    Product Month Number
    a jan 654
    b feb 234
    c mar 123
    d apr 543
    e may 765
    f jun 876
    g jul 345
    h aug 567
    i sep 532
    j oct 111
    k nov 423
    l dec 776


    Product Month Number
    a jan 123
    b feb 234
    c mar 654
    d apr 564
    e may 556
    f jun 234
    g jul 987
    h aug 765
    i sep 123
    j oct 443
    k nov 554
    l dec 765

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Consolidate two data sets

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    01-23-2014
    Location
    Bridges misssse
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Consolidate two data sets

    OK i have attached the spreadsheet. Solution would assume that the data in the underlying data sets is not sorted and so a simple sum of the reference cells would not be appropriate.
    Attached Files Attached Files

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Consolidate two data sets

    Just use Pivot Table Multiple Consolidation Ranges which is the rightly suitable method for your data structure

  5. #5
    Registered User
    Join Date
    01-23-2014
    Location
    Bridges misssse
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Consolidate two data sets

    I tried that but I can't get it to work as my data is not in cross tab format (it's essentially a list not a table)

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: Consolidate two data sets

    Maybe something like:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to consolidate data from multiple data from worksheets into on worksheet
    By neilclelland in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-05-2013, 11:05 AM
  2. consolidate data from multiple files/worksheets when data in variable location
    By Aaron_Tram in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-21-2013, 12:49 PM
  3. VBA to fill down for multiple data sets, then consolidate data
    By franklin225 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-07-2012, 02:37 PM
  4. Consolidate opposite pairs with data into one common pair with combined data
    By cpbrock3 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-29-2011, 12:00 PM
  5. Two sets of data for X-axis and 2 sets of data for Y
    By lord_jagganath in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 12-07-2009, 07:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1