+ Reply to Thread
Results 1 to 2 of 2

Consolidating data within a table

  1. #1
    Registered User
    Join Date
    09-14-2020
    Location
    UK
    MS-Off Ver
    2016
    Posts
    1

    Consolidating data within a table

    Hello,

    I am trying to consolidate sales data within a table so that I can see the total revenue for each Product Segment by consolidating multiple Items.

    I have attached an example based on a much larger data set.

    I am looking to consolidate the data so that I can see the revenue each month and total amount (column S) for each Product Segment from each Company.

    For example, I would be able to consolidate the data for Company A Kits and see that the total FY Amount is 12,866.01

    In addition, I am looking to combine data from separate tables which cover different periods of time and some different products. For example, I would wish to combine the data on sheet 2 with the data in sheet 1. Some of the products (0001 and 0002) also appear on the table on sheet 1, while others (0051) appear only on sheet 2. Is there a way to combine such data?

    Any help would be much appreciated, my actual data set is well over 7,000 entries so I'm looking for an automated way to combine data.

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Consolidating data within a table

    Hello athompson1298 and Welcome to Excel Forum.
    As you are using the 2016 version of Excel you could use Get & Transform (Power Query) to produce a row over row data set from which you could make pivot tables.
    The first step would be to convert the ranges on sheet1 and sheet2 into Excel tables (Ctrl + t)
    By default Excel should name them Table1 and Table2.
    The following Power Query advanced editor code will produce a row over row table (default Query1) as modeled on the PQ Table sheet.
    Please Login or Register  to view this content.
    The PQ Table may then be used to produce pivot tables (as modeled on Pivot Table sheet)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Consolidating Data into one Pivot Table/Chart
    By aserle in forum Excel General
    Replies: 6
    Last Post: 02-28-2018, 11:08 PM
  2. [SOLVED] consolidating table data
    By jrtaylor in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-05-2017, 02:02 PM
  3. Consolidating Data From a Table to a Graph
    By kfridell in forum Excel General
    Replies: 2
    Last Post: 05-25-2016, 07:23 PM
  4. Consolidating and converting table data - Help!
    By forbzinator in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-20-2014, 10:46 AM
  5. Consolidating data with a chart or pivot table
    By ehitt in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-24-2013, 11:08 PM
  6. Consolidating data for pivot table
    By floricita in forum Excel General
    Replies: 0
    Last Post: 09-01-2010, 09:03 AM
  7. Consolidating data in one Pivot Table.
    By Kianuz in forum Excel General
    Replies: 2
    Last Post: 10-23-2009, 10:24 AM

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