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