+ Reply to Thread
Results 1 to 18 of 18

Best way to consolidate list/totals from worksheets with different column layouts

  1. #1
    Registered User
    Join Date
    10-01-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    12

    Best way to consolidate list/totals from worksheets with different column layouts

    I have 5 worksheets in an Excel 2016 spreadsheet, each containing a list of investments. All of the sheets have columns in common (e.g. description, quantity, value), but these columns are arranged differently in each sheet (e.g. the quantity may be column A in the first sheet, but column C or D in the others). Each row contains details of a different investment, and the same investment may appear in more than one worksheet.
    I want to produce a single consolidated list of all investments from these worksheets, with a sum of quantities and valuations for each.
    I don't want to re-order the columns in each sheet, as they are copied and pasted regularly from an external source.
    I've been trying out pivot tables with the Multiple consolidation ranges option, but can't get it to work because of the inconsistency in the column layouts between worksheets.
    Can anyone suggest the best way of getting a consolidated list from these worksheets? I also have access to MS Access 2016, if that would be of any use?

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Best way to consolidate list/totals from worksheets with different column layouts

    I am not an expert in this field but Power Query is the way to go.

    In the Data Tab. Click on "From Table or Range"

    Then import each of your sheets into Power Query.

    As the Titles Match Power Query should sort the columns issue out for you automatically.


    ok. I got this to work.

    I created a workbook with two similar tables on two sheets and saved it to my Desktop as PQ1 and then I closed it.

    I opened a new workbook.. Selected the Data Tab and then Selected "Get Data"

    I selected the Option "From file" and then "From Workbook" I browsed and selected PQ1.

    I selected "select multiple items" and selected Sheet1 and Sheet2 and clicked on Load

    A window opens "Queries and Connections" that lists the two sheets.

    I right click on Sheet1 and select append.

    Another window opens. I select "two tables" then I select Sheet1 and Sheet2 and click on Ok

    Power Query merges my two tables.

    So Save the table I select "File" and then "Close & Load"
    Attached Files Attached Files
    Last edited by mehmetcik; 11-04-2020 at 02:48 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Best way to consolidate list/totals from worksheets with different column layouts

    It would also help if you attached a sample Excel workbook. Details of how to do so are given in the yellow banner at the top of the screen.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    10-01-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Best way to consolidate list/totals from worksheets with different column layouts

    Thanks - here's an example of what I mean (attachment)
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-01-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Best way to consolidate list/totals from worksheets with different column layouts

    Quote Originally Posted by mehmetcik View Post
    I am not an expert in this field but Power Query is the way to go.

    In the Data Tab. Click on "From Table or Range"

    Then import each of you sheets into Power Query.

    As the Titles Match Power Query should sort the columns issue out for you automatically.
    In the Data Tab (under "New Query"?) I don't see an option for ""From Table or Range". I only see options for various types of external sources. Am I looking in the right place?

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Best way to consolidate list/totals from worksheets with different column layouts

    I see that you have one fund (ARTEMIS FUND MANAGERS US ABST RETURN HGD I ACC NAV) which appears in Account1 and in Account 3. How would you like this to be shown? As one consolidated row, or as two separate rows ?

    They have different Gains, so if consolidated you will have to point out how this could be achieved (manually).

    Hope this helps.

    Pete

  7. #7
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Best way to consolidate list/totals from worksheets with different column layouts

    Open the data tab.

    On the far left is "Get Data" select that and then "From File" and then "From Workbook"

    Good Luck

  8. #8
    Registered User
    Join Date
    10-01-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Best way to consolidate list/totals from worksheets with different column layouts

    Quote Originally Posted by Pete_UK View Post
    I see that you have one fund (ARTEMIS FUND MANAGERS US ABST RETURN HGD I ACC NAV) which appears in Account1 and in Account 3. How would you like this to be shown? As one consolidated row, or as two separate rows ?

    They have different Gains, so if consolidated you will have to point out how this could be achieved (manually).

    Hope this helps.

    Pete
    I'd like this to show as a single consolidated row, with a sum of the quantity and valuation columns (I don't need to include any of the other quantitative columns)

  9. #9
    Registered User
    Join Date
    10-01-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Best way to consolidate list/totals from worksheets with different column layouts

    Quote Originally Posted by mehmetcik View Post
    Open the data tab.

    On the far left is "Get Data" select that and then "From File" and then "From Workbook"

    Good Luck
    I'm not seeing those options - see attached screenshot
    Attached Images Attached Images

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Best way to consolidate list/totals from worksheets with different column layouts

    I think that would be too complex for a formula solution (which I was considering), so I'll leave off this thread and hope you get something from the Power Query approach.

    Pete

  11. #11
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Best way to consolidate list/totals from worksheets with different column layouts

    Do you have a "Get Data" option?

    If so Right Click on it at select "Data Source Options" see if you have "Global options" enabled.

  12. #12
    Registered User
    Join Date
    10-01-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Best way to consolidate list/totals from worksheets with different column layouts

    Quote Originally Posted by mehmetcik View Post
    Do you have a "Get Data" option?

    If so Right Click on it at select "Data Source Options" see if you have "Global options" enabled.
    No - I can only see a "Get External Data" option

  13. #13
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Best way to consolidate list/totals from worksheets with different column layouts

    Ok.

    What is under "Other Sources"?

  14. #14
    Registered User
    Join Date
    10-01-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Best way to consolidate list/totals from worksheets with different column layouts

    Quote Originally Posted by mehmetcik View Post
    Ok.

    What is under "Other Sources"?
    I posted a screenshot of this with post #9

  15. #15
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Best way to consolidate list/totals from worksheets with different column layouts

    Ok.

    It must be there somewhere. Try clicking on "From Table" and see if its hidden there. Otherwise see if there is anything useful in the help option.

    I am sorry I only have access to the 365 version of excel.
    Last edited by mehmetcik; 11-04-2020 at 05:08 PM.

  16. #16
    Registered User
    Join Date
    10-01-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Best way to consolidate list/totals from worksheets with different column layouts

    Quote Originally Posted by mehmetcik View Post
    Ok.

    It must be there somewhere. Try clicking on "From Table" and see if its hidden there. Otherwise see if there is anything useful in the help option.

    I am sorry I only have access to the 365 version of excel.
    OK I see an option to select a range when I select "From Table", but I don't see how I can use multiple selections (with different column layouts)
    ???

  17. #17
    Forum Contributor
    Join Date
    12-17-2013
    Location
    ON, Canada
    MS-Off Ver
    MS 365
    Posts
    171

    Re: Best way to consolidate list/totals from worksheets with different column layouts

    itm,

    Just a suggestion:
    Convert all your lists into Excel tables;
    Load each table into Power Query as a connection only;
    Append all Queries;
    Close and load to a new worksheet.

    Not sure where the "Cash" fits in or what the "*" and "p" mean.
    Hope this helps.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    10-01-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Best way to consolidate list/totals from worksheets with different column layouts

    Quote Originally Posted by Flyboy65 View Post
    itm,

    Just a suggestion:
    Convert all your lists into Excel tables;
    Load each table into Power Query as a connection only;
    Append all Queries;
    Close and load to a new worksheet.

    Not sure where the "Cash" fits in or what the "*" and "p" mean.
    Hope this helps.
    Many thanks for that - I think that approach will get me there. I've just discovered an anomaly in my data which will need a tweak - hopefully Power Query can also handle that. As it's a different and specific issue I'll open a new thread for it.

+ 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. Multiplying column layouts in dozens subtables?
    By MasN76 in forum Excel General
    Replies: 1
    Last Post: 12-19-2019, 05:14 AM
  2. Possible To Save Pivot Table Column Layouts?
    By seethesun in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-27-2018, 10:37 AM
  3. Function to consolidate totals
    By managingcrap in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-11-2014, 08:50 AM
  4. [SOLVED] Need a formula to consolidate a 2 column list
    By mhedge in forum Excel General
    Replies: 8
    Last Post: 08-15-2014, 08:44 PM
  5. [SOLVED] Consolidate totals from data
    By Lozhaze in forum Excel General
    Replies: 2
    Last Post: 08-30-2012, 06:03 AM
  6. Consolidate Unique List Items into an Adjacent Column
    By packetdog in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-09-2012, 11:29 AM
  7. [SOLVED] consolidate totals
    By r-boneinc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-19-2006, 11:35 AM

Tags for this Thread

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