If anyone can help me figure this out, much would be appreciated. It seems rather difficult since I cannot use formulas in arrays and I am wanting to avoid using VBA. I want to be able to list all unique data based on their sums, their type (i.e. peach, apple, etc.), and listing highest sums and their associated fruit to lowest (Similar to Column O in the excel file). I want to also list amount rejected next to that cell. Not sure if my formulas are most efficient but it seems to work in steps which I just want to eliminate having to show each number and category manipulation and provide the finalized manipulation. How would I go about doing this?
I am also wanting to avoid using filters and auto-sorting. There is a large amount of data I must enter in, the excel is just an example to represent what I'm trying to finalize.
I also want to leave excess cells blank when there is no more unique categories.
Edit: Another constraint is that the categories are not always the same, so I cannot count on "Apples" being there, it may be "Red Delicious Apples", "Granny Smith", etc.
Edit: If you cannot open the Excel file, Total quantity is entered in Column D, Qty Rejected is in Column E, the category is in Column F. As of right now I have a function that lists all of the unique categories in Column J based on what is entered in, in Column F.
Based on the unique categories I find the sum, or in this case the sumproduct, of those categories in Columns K and L.
I take it another step and make Column P list the categories from their largest sum quantities to lowest.
I want to eliminate having to go from column's J, K, L to column P. I want to eliminate having to use columns J, column K, and column L so that column P, Q and R will be the refined, manipulated data from highest sum to lowest sum using the raw data inputed in columns D, E and F.
Not sure if this is possible or not. If it is, I am unsure if finding the highest sum associated with a general, unspecified unique category would work or finding and displaying a unique category with the highest sum category listed first.
Edit: An example of this would be if Apricots had a total qty sum of 733 but it's listed on the 6th row of column J, I want it to be listed in the 2nd row of column J as highest total qty sum. Bananas would be listed below that, etc.
Attachment 157096
Attachment 157097
Side note: I posted in the General forum no knowing there was this Worksheet Function section. Sorry, forum moderators.
Bookmarks