+ Reply to Thread
Results 1 to 2 of 2

Pivot from multiple sources without powerquery or tables

  1. #1
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 365
    Posts
    376

    Pivot from multiple sources without powerquery or tables

    Hi all

    Looking for a clean solution to creating a pivot based off two worksheets but I've tried the following and was wondering what other ideas people had:

    Table relationships - Usually I'd make sure both were in tables and create relationship between the common field in both. I've got two data sets where the common field has multiple rows against each so excel says there is duplicates and therefore can't create the relationship

    Power Query - Our IT systems block installations so I can't install powerquery without authorisation but have used it before and appreciate this would work

    Manual solution - I could create a manual list with no duplicates of the unique field from each table and do sumproducts to pull the figures I'm interested in from each table

    I'm trying to make it as automated as possible to avoid any user errors so just wondering what other solutions people were using? Hopefully I don't need to go down the manual route suggested above

    Thanks!

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

    Re: Pivot from multiple sources without powerquery or tables

    As opposed to manually producing the distinct list, there are formulas that can produce a list of distinct items such as:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Column A contains all possible items that could be used for pulling the figures, and the formula is placed in cell XFD2, or elsewhere, and copied down far enough that the user would not be expected to copy further under normal conditions.
    Let us know if you have any questions.
    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. Excel2016 - PowerQuery (Multiple Data Sources)
    By Sharp2k in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 10-28-2020, 11:18 AM
  2. [SOLVED] VLOOKUP from multiple sources AND pivot tables/charts
    By BigErnKingpin in forum Excel General
    Replies: 0
    Last Post: 10-03-2020, 02:53 PM
  3. Macro to update pivot table filters from multiple pivot tables of different data sources
    By groblerdn85 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-24-2017, 05:10 AM
  4. Replies: 0
    Last Post: 07-15-2017, 01:50 PM
  5. VBA Pivot Table - Multiple Tables - Multiple Sources of Data
    By ryanmorris in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-08-2012, 03:13 PM
  6. Pivot Tables comming from two data sources
    By rbw in forum Excel General
    Replies: 2
    Last Post: 08-31-2007, 05:32 AM
  7. pivot tables using two mdb data sources
    By jack in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-14-2006, 02:15 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