+ Reply to Thread
Results 1 to 7 of 7

Summarise multiple excel worksheets into one sheet

  1. 12-19-2022, 01:57 AM #1
    bob112233
    bob112233 is offline
    Forum Contributor
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    365
    Posts
    108

    Angry Summarise multiple excel worksheets into one sheet

    Hi,

    I have multiple excel sheets names (raw data 1, raw data 2 etc) and each sheet as the following headers (ID, Surname, First Name, Job, Q1,Q2,Q3,Q4)

    Q1,2,3,4 = Quarterly Earning

    I want to combine all my raw data sheets (1,2 ... n) such that I can easily identify those individuals with the lowest and highest Q1,Q2,Q3,Q4.

    I currently don't have any desired results because I can't figure out how to display my final results. All I know that I need to be able to filter by first name, surname to identify those individuals with the lowest or highest Q1,2,3 and 4.

    Any help or guidance is greatly appreciated.

    PS- I will try to come up with some desired results.

    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 12-19-2022, 02:41 AM #2
    tanasedn
    tanasedn is offline
    Valued Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2021, 365 v 2208
    Posts
    722

    Re: Summarise multiple excel worksheets into one sheet

    One solution with Power Query
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Diana Tanase

    If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !
    Reply With QuoteRegister To Reply
  3. 12-19-2022, 03:40 AM #3
    avk
    avk is offline
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,222

    Re: Summarise multiple excel worksheets into one sheet

    In sheet Data Simplifed in "B2" using Data Validation sheet name
    In "B3"
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    another way in "B8"
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    by simultaneous pressing shift+ctrl+enter
    Attached Files Attached Files

    atul

    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
    Reply With QuoteRegister To Reply
  4. 12-19-2022, 04:14 AM #4
    bob112233
    bob112233 is offline
    Forum Contributor
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    365
    Posts
    108

    Re: Summarise multiple excel worksheets into one sheet

    What happens when I add more excel sheets?

    Also, how can i filter based on first name or job title?

    I need to organise this data so that I can easily identify all bottom 10% of Q1,2,3,4 earning.
    Reply With QuoteRegister To Reply
  5. 12-19-2022, 04:31 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,959

    Re: Summarise multiple excel worksheets into one sheet

    B2, copied down:
    =TAKE(SORT(DROP(CHOOSECOLS(VSTACK('Raw Data 1'!$A$1:$H$5,'Raw Data 2'!$A$2:$H$5,'Raw Data 3'!$A$2:$H$5),1,2,3,4,MATCH($A2,'Raw Data 1'!$A$1:$H$1,0)),1),5,-1),1)

    B8, copied down:
    =TAKE(SORT(DROP(CHOOSECOLS(VSTACK('Raw Data 1'!$A$1:$H$5,'Raw Data 2'!$A$2:$H$5,'Raw Data 3'!$A$2:$H$5),1,2,3,4,MATCH($A2,'Raw Data 1'!$A$1:$H$1,0)),1),5,1),1)
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  6. 12-19-2022, 05:46 AM #6
    bob112233
    bob112233 is offline
    Forum Contributor
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    365
    Posts
    108

    Re: Summarise multiple excel worksheets into one sheet

    This solution is really interesting, i haven't thought about it this way.

    Is there a way to modify low and high by assigning a range? say between 0-10 or 90-110?
    Last edited by bob112233; 12-19-2022 at 05:53 AM.
    Reply With QuoteRegister To Reply
  7. 12-19-2022, 05:50 AM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,959

    Re: Summarise multiple excel worksheets into one sheet

    To add more sheets, add them in - comma separated - into this bit:

    VSTACK('Raw Data 1'!$A$1:$H$5,'Raw Data 2'!$A$2:$H$5,'Raw Data 3'!$A$2:$H$5)

    Note that the FIRST one includes the header row, subsequent ones DO NOT.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] I am trying to summarise worksheets in a workbook
    By Pickygame in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-24-2019, 02:50 PM
  2. Summarise data in one sheet from multiple sheets
    By rajeev.raj in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-20-2018, 03:18 PM
  3. Summarise Multiple Sheets into One Sheet and Filter by a value
    By christoph13 in forum Excel General
    Replies: 2
    Last Post: 12-26-2016, 12:37 PM
  4. Summarise multiple sheets into master sheet: text, numbers
    By smellodeee in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-19-2016, 08:24 AM
  5. [SOLVED] Search multiple worksheets for employee no.; count leave taken; summarise one worksheet
    By moshjosh in forum Excel General
    Replies: 2
    Last Post: 12-09-2012, 05:58 PM
  6. create sheet to summarise invoices on other worksheets
    By [email protected] in forum Excel General
    Replies: 1
    Last Post: 05-22-2006, 02:35 PM
  7. [SOLVED] How to summarise data in the same place in multiple worksheets?
    By Peter Oz 67 in forum Excel General
    Replies: 4
    Last Post: 04-09-2006, 11:50 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1