Results 1 to 8 of 8

Thread: Help Formula to replace pivot

  1. #1

    Help Formula to replace pivot



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I need urgent help to replace a pivot that I have created for reporting
    The data sheet has more than 20,000 rows and 65 columns. The main information forming this reports come from 4 columns - e.g Classification, Sub Classification, Category & Count
    Report needs to populate Top 5 Records from each subclassification with Category name and Count
    e.g.
    Classification Sub Classification Category Count

    Apple Golden Delicious Fresh 50,000
    2days-old 23,000
    Week-old 20,000
    Unclassified 10,000

    Pink Lady 2days-old 30,000
    Week-old 20,000
    Fresh 2,000
    Unclassified 10,000

    Orange Navel Fresh 45,000
    2days-old 32,000
    Week-old 20,000
    ..... & so on

    The formula is required to populate top 5 records at the right. The classification and Subclassification can remain fixed in the report table in a new Tab as that would not change quite often, hence the formula to populate Column 3 & 4 in the example above is desparately needed.
    Any Help is really apprecaited.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    Hi there,

    Maybe I'm not following exactly, but if you're looking for the top 5 items, why not just filter the PivotTable to the top 5? I'm not sure what version of Excel you're using, but in Excel 2010 (and 2007 I believe), you can do this by clicking one of the dropdown filters on the table, selecting Value Filters --> Top 10, and then you can set how many you want. I'd probably just set up a top 5 table for each view I needed.

    For a more detailed walkthrough, Debra Dalgleish has an excellent article on her site of working with Top 10 filters: http://www.contextures.com/excel-piv...ers-top10.html

    Hope this helps,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    It works by using pivot But I have to copy/paste for 38 mini tables created for reporting in a separate tab within same spreadsheet on weekly basis. And there is always risk of manual error while doing that, as it often happens. therefore I want to use formula rather. I have attached the example in attachment as actual data is lot more...
    Thanks
    Attached Files Attached Files

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    Okay, fair enough.

    Not sure exactly which version of Excel you're using so I've attached a copy using SUMIFS and SUMPRODUCT to complete the "Top 5" section for you. If the SUMIFS column comes up with #NAME?, then skip that and just use SUMPRODUCT.

    From here you should be able to apply the same logic to the Bottom5 section.

    These formulas key off the clasification, sub class and category to return the number.

    With regards to making the category dynamic, will the subclass also be dynamic? Are there always five values per category?
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Sorry Paul, SumIF and Sumproduct Formula is looking all three columns and populating the corresponding value by matching three columns.
    =SUMPRODUCT(--($B$4:$B$43=G$8),--($C$4:$C$43=H$8),--($D$4:$D$43=I8),$E$4:$E$43)

    But We won't know what would in cell ref =I8 , it has to bethe category of one of the top the five values in array $E$4:$E$43, hence the formula needs to find the Top value in array and populate the category in 3rd column and corresponding value in the fourth column.
    In the formula above this part " --($B$4:$B$43=G$8),--($C$4:$C$43=H$8)," is fine
    but we need to find Highest value from $E$4:$E$43 and display corresponding name from $D$4:$D$43 (from same row) in I8 and value in J8
    Thanks

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    Right... OK.

    I'm going to have to think on this one... Just trying to figure out how to extract the text from the ranked subset.

    Are you okay with inserting a helper column or table on another sheet if necessary?

    I'm also curious... and this is important... your data... is it always in the exact same order, or could the Oranges get mixed up? This drives how dynamic the formula needs to be...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    Actually, have a look at the attached.

    I had to add two helper columns to rank the items appropriately, and the formulas for the class need to be updated for each class (the range to check), but as long as your data table is consistent, this should work.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Hi I have just had a look and shall try this on my data file. I hope it works. Thanks a lot for your wonderful help.
    Is it possible to remove sheet1 from the files uploaded?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •