Help Formula to replace pivot

[Gill26]

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.

[Ken Puls]

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,

[Gill26]

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

[Ken Puls]

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?

[Gill26]

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

[Ken Puls]

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]

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.

[Gill26]

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?