+ Reply to Thread
Results 1 to 13 of 13

Consolidate data from multiple sheets from specific ranges that can be in dynamic length

  1. #1
    Registered User
    Join Date
    03-25-2019
    Location
    India
    MS-Off Ver
    Office 365 - 2019
    Posts
    10

    Post Consolidate data from multiple sheets from specific ranges that can be in dynamic length

    Hi Forum,

    Need your help in making a master excel sheet that will consolidate data(textual data- names) from multiple sheets from a defined range ( but these ranges can be dynamic as in each sheet the total number of names will be different).

    It is a 9 box model used in HR, as the different sheets are sent out to different managers (Each manager has a different organization size and therefore the size of the individual ranges may be different) and also consolidating every name in the master sheet has to be dynamic as we would have no control on where an employee would be placed by their manager.

    There is another thread on this forum for a 9 box chart (not able to attach a link) that has worked on something similar but my requirement is little different. I have also attached the sample document.

    The data has to be consolidated from the input sheet 1,2 (this can go upto 25 sheets) into the master sheet, so all employees in a current grid in all the input sheets have to be consolidated in the same grid on the master sheet.

    Thank you for your help.

    Regards,
    Attached Files Attached Files

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

    Re: Consolidate data from multiple sheets from specific ranges that can be in dynamic leng

    This proposal employs Power Pivot, also known as Get & Transform, which is found on the data tab in the 2019 version of Excel.
    Power Pivot is used to consolidate data from two, or more, tables into a single table on the 'Both' sheet from which the 9 box chart may be populated.
    The formulas used in populating the 9 box chart are similar to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that the proposal manually transforms the ranges in columns A on the two 'Input' sheets into Excel tables.
    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.

  3. #3
    Registered User
    Join Date
    03-25-2019
    Location
    India
    MS-Off Ver
    Office 365 - 2019
    Posts
    10

    Re: Consolidate data from multiple sheets from specific ranges that can be in dynamic leng

    Hi Sir,

    Thank you for your response. Can you please help me clear these doubts-

    1. I was thinking of power queries but the issue i am facing is that there will be around 20-25 input sheets as there are around 600+ employees. How do i consolidate that in one sheet(because each grid length can be different as per employee mapping in the individual sheets).

    2. Also the Rating score in each data table on the input sheets has to be done manually or are we using any IF or match condition to arrive at that?

    3. On the master sheet, if there are 600+ employees will each grid/box have to be of 600+ rows to accommodate all the employees, is there a way to make it of dynamic length.

    Thank you again for all the help. Really appreciate it.

    Regards,

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

    Re: Consolidate data from multiple sheets from specific ranges that can be in dynamic leng

    As to #1.
    From the Data tab > Get & Transform Data pane > choose From Table/Range.
    When the Power Query Editor pops up select Append Queries.
    When the Append dialog box appears choose Three or more tables.
    All of you tables should be in the Available tables window. Select the tables you want to combine and select Add>>
    As to #2
    Employees were placed in the 9 box charts in each of the 'Input' sheets so I assume there is already a process in place to decide on how that is done.
    As to #3
    If you want the entire 9 box chart to be visible, it would seem to me that the way to handle that is to have a scroll bar in each section. I feel that there may be contributors that could help in the Excel Charting & Pivots forum.
    Once part #1 of post #3 has been answered and the thread is marked solved (thread tools menu above your first post), then open a new thread on that forum.
    Let us know if you have any questions.

  5. #5
    Registered User
    Join Date
    03-25-2019
    Location
    India
    MS-Off Ver
    Office 365 - 2019
    Posts
    10

    Re: Consolidate data from multiple sheets from specific ranges that can be in dynamic leng

    Hi JeteMC,

    On Point 2, I was referring to the numerical assignment arrived in the data table, for example in Input sheet 1, Employee3 is in the 2nd box - how did rating "2" come in the data table? are you using any formula or manually typing/assigning it? This was my original question.

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

    Re: Consolidate data from multiple sheets from specific ranges that can be in dynamic leng

    When I looked at 'Input Sheet 1', 'Employee 3' was listed in cell F7. Looking at the '9 Box Graphic' sheet that position corresponds to '2 Superior Contributor'. So when after producing the table the value of '2' was typed into cell L7, corresponding to 'Employee 3'.
    Let us know if you have any questions.

  7. #7
    Registered User
    Join Date
    03-25-2019
    Location
    India
    MS-Off Ver
    Office 365 - 2019
    Posts
    10

    Re: Consolidate data from multiple sheets from specific ranges that can be in dynamic leng

    Quote Originally Posted by JeteMc View Post
    When I looked at 'Input Sheet 1', 'Employee 3' was listed in cell F7. Looking at the '9 Box Graphic' sheet that position corresponds to '2 Superior Contributor'. So when after producing the table the value of '2' was typed into cell L7, corresponding to 'Employee 3'.
    Let us know if you have any questions.
    So to confirm it was manually typed by you?

    Is there a way we can automate that as well, as in if someone is placed in a particular box, the appropriate rating gets populated in the table?

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

    Re: Consolidate data from multiple sheets from specific ranges that can be in dynamic leng

    Yes, manually typed.
    It would seem simpler to type a number in the 'Rating' column than to type a name in one of the boxes, since the person typing the name would probably have to know the number as listed in the '9 box chart'.
    However there are ways to do what you ask. One would be to add three helper columns, which may be moved and/or hidden for aesthetic purposes.
    The helper columns are populated using: =IFERROR(CHOOSE(ROUNDUP(MATCH(Table2[@Employee],E$7:E$36,0)/10,0),3,6,9),"")
    The 'Rating' column is populated using: =IF(SUM(M5:O5)=0,"",SUM(M5:O5))
    Let us know if you have any questions.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-25-2019
    Location
    India
    MS-Off Ver
    Office 365 - 2019
    Posts
    10

    Re: Consolidate data from multiple sheets from specific ranges that can be in dynamic leng

    Hi,

    I changed the input methodology, so now instead of copy-pasting an employee in a box, we are using ratings for performance and potential to arrive at the score and then populate them in the grid. Sheet "With Chart". The problem here is there will be around 600+ people and that will be a lot in the graph.

    It would be great if you could help in getting the employee names in the colored boxes in the sheet "with scroll bar". I am not able to make your aggregate index function work here.

    Please help.
    Attached Files Attached Files
    Last edited by mujahidreza; 04-03-2019 at 06:23 AM.

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

    Re: Consolidate data from multiple sheets from specific ranges that can be in dynamic leng

    The formulas are similar to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    As I stated in post #4, you may need to ask the contributors on the Excel Charting & Pivots forum for help with scroll bar application.
    Let us know if you have any questions (about the formulas).
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    03-25-2019
    Location
    India
    MS-Off Ver
    Office 365 - 2019
    Posts
    10

    Re: Consolidate data from multiple sheets from specific ranges that can be in dynamic leng

    okay thank you

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

    Re: Consolidate data from multiple sheets from specific ranges that can be in dynamic leng

    You're Welcome. If that resolves this part of the query please take a moment to mark the thread as 'Solved' using the thread tools menu above your fist post. I hope that you have a blessed day.

  13. #13
    Registered User
    Join Date
    03-25-2019
    Location
    India
    MS-Off Ver
    Office 365 - 2019
    Posts
    10

    Re: Consolidate data from multiple sheets from specific ranges that can be in dynamic leng

    Done and Thank you again, Hoping to get help on the charting forum with the scroll bars.

+ 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. How to combine dynamic ranges from multiple sheets into one list?
    By mst3k in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-07-2015, 03:25 AM
  2. Consolidate multiple data ranges to an existing Pivot Table
    By cs02 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-11-2015, 02:48 PM
  3. Replies: 1
    Last Post: 06-30-2014, 09:17 AM
  4. Main sheet populated by multiple sheets, dynamic ranges
    By cmack in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-20-2014, 09:14 AM
  5. [SOLVED] how do i use import to consolidate data from multiple sheets
    By Naveen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-14-2006, 07:45 AM
  6. [SOLVED] consolidate data, dynamic ranges
    By Excel GuRu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-28-2006, 09:50 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