Create and sort distinct list by adjacent cell value size
Question: How do I create a distinct list from another list and then sum adjacent value and sort distinct list from large to small? (See picture to the right)
Answer: I think you can do that with Pivot tables. But here is a formula to sort distinct values from large to small, see picture below (C2:C10):
=INDEX(List, MATCH(LARGE(SUMIF(List, INDEX(List, SMALL(IF(MATCH(List, List, 0)=(ROW(List)-ROW(List_start)+1), MATCH(List, List, 0), ""), ROW(List)-ROW(List_start)+1)), List_amount), ROW(List)-ROW(List_start)+1), SUMIF(List, List, List_amount), 0)) + Ctrl + Shift + Enter
Formula in (D2:D10):
EDIT: =SUMIF(List;C2;List_amount) + Ctrl + Shift + Enter copied down to D10.
=IF(ROW(List)-ROW(List_start)+1<=COUNT(IF(MATCH(List, List, 0)=ROW(List)-ROW(List_start)+1, MATCH(List, List, 0), "")), LARGE(SUMIF(List, INDEX(List, SMALL(IF(MATCH(List, List, 0)=ROW(List)-ROW(List_start)+1, MATCH(List, List, 0), ""), ROW(List)-ROW(List_start)+1)), List_amount), ROW(List)-ROW(List_start)+1), "") + Ctrl + Shift + Enter
There are some named ranges in this article:
List (A2:A10)
List_start (A2)
List_amount (B2:B10)
To customize formulas to your workbook, change named ranges.
Download excel sample file for this article.
unique-list-sorted-by-size.xls
(Excel 97-2003 Workbook *.xls)
The downside with this formula is that if there are two or more summed values that are identical , the wrong value will be matched in column A. Resulting not creating a distinct list. This problem might be solved in an upcoming article.
Functions in this article:
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
SMALL(array,k) returns the k-th smallest row number in this data set.
ROW(reference) returns the rownumber of a reference
MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value
COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers
LARGE(array,k) returns the k-th largest row number in this data set
Related posts:
Create a list of distinct values from a list where an adjacent cell value meets a criteria in excel
Create a unique distinct list and sort by occurrances from large to small
Create a list of duplicates where adjacent cell value meets a condition in excel
Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
Create unique distinct list from column where an adjacent column meets criteria

















