+ Reply to Thread
Results 1 to 10 of 10

Need Consolidation from sheet 1 to sheet 2

  1. #1
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    183

    Need Consolidation from sheet 1 to sheet 2

    I want to display total output in numbers in sheet2 from the sheet 1 input
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    35,017

    Re: Need Consolidation from sheet 1 to sheet 2

    Please provide expected results (manually added) for the first 10-15 rows of the results table.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    183

    Re: Need Consolidation from sheet 1 to sheet 2

    we should not add manually in sheet2. we need to put formula in sheet2 and it should display the output total numbers that how how much total numbers are there in the given region1 and region 2 in the specific product type given

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2019 (Win 10 - Work) & 365 Subscription Insider (Win 10 - Home)
    Posts
    35,017

    Re: Need Consolidation from sheet 1 to sheet 2

    Precisely, so show us what you are expecting, then we can offer a formula.

    You really should understand how this works by now!

  5. #5
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    183

    Re: Need Consolidation from sheet 1 to sheet 2

    i have attached sample output here. i need the output in all cell it will bring from formula
    Attached Files Attached Files

  6. #6
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,186

    Re: Need Consolidation from sheet 1 to sheet 2

    You could use a COUNTIFS formula, something like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The problem with this is that you must manually maintain the row / column headers, to make sure all Regions and Product Types are included.

    Much better to use a pivot table. See attached example.
    Attached Files Attached Files
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  7. #7
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    183

    Re: Need Consolidation from sheet 1 to sheet 2

    it works nice but i attached one more column, can you pls fill the same and send
    Attached Files Attached Files

  8. #8
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,186

    Re: Need Consolidation from sheet 1 to sheet 2

    You can make that change yourself. Select the pivot table, and in the Field List, drag field "branch name" into the ROWS section.

  9. #9
    Forum Contributor
    Join Date
    04-11-2017
    Location
    Madurai
    MS-Off Ver
    2016
    Posts
    183

    Re: Need Consolidation from sheet 1 to sheet 2

    i don't know pivot table so pls in MIS2 sheet F4 = what formula i want to put?

  10. #10
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,186

    Re: Need Consolidation from sheet 1 to sheet 2

    You don't know the formula, either. So, given you need to learn SOMETHING, I suggest learning the pivot table.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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