Create a unique distinct list and sort by occurrances from large to small
Question: How do I create a new unique distinct list originating from a cell range. (See picture to the right.) I also 
want the list sorted from large to small by the number of occurrances?
Answer: Here is the array formula in C2:C13. See picture below. =INDEX(List, MATCH(LARGE(IF(MATCH(List, List, 0)=ROW()-ROW(Unique_start)+1, COUNTIF(List, List)+ROW()/1048576, ""), ROW()-ROW(Unique_start)+1), IF(MATCH(List, List, 0)=ROW()-ROW(Unique_start)+1, COUNTIF(List, List)+ROW()/1048576, ""), 0)) + Ctrl + Shift + Enter
Formula in D2: =IF(ISTEXT(C2), COUNTIF(List, C2), "") copied down to D13. See picture below.
Named ranges
List (A2:A13)
Unique_start (C2)
Download excel example file.
unique-sorted-by-occurances.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
ROW(reference) Returns the rownumber of a reference
LARGE(array,k) returns the k-th largest row number in this data set
MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition
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
This blog article is one out of thirteen articles on the same subject "unique".
- How to extract a unique distinct list from a column in excel
- Extract a unique distinct list from two columns using excel 2007 array formula
- Extract a unique distinct list from three columns in excel
- Extract distinct unique sorted year and month list from a date series in excel
- Create a unique distinct list from a date range in excel
- Unique values from multiple columns using array formulas
- Extract a unique distinct list sorted from A-Z from range in excel
- Sort a range by occurence using array formula in excel
- Filter unique distinct values from two ranges combined in excel 2007
- Create a unique list and sort by occurrances from large to small
- Unique list to be created from a column where an adjacent column has text cell values
- Create unique list from column where an adjacent column meets criteria
- How to create a unique distinct list where other columns meet two criteria
Related posts:
- How to create a unique distinct list where other columns meet two criteria
- Create a unique distinct list from a date range in excel
- Create and sort distinct list by adjacent cell value size
- Unique distinct list sorted based on occurrance in a column in excel
- Extract a unique distinct list sorted from A-Z from range in excel
- Unique distinct list from a column sorted A to Z using array formula in excel
- Extract a unique distinct list sorted alphabetically removing blanks from a range in excel
- Extract a unique distinct list from three columns in excel
- Create unique distinct list from column where an adjacent column meets criteria
- Create unique list from two columns
September 19th, 2009 at 4:56 am
hi
how to find missing numbers range 1-49 from following line
06 11 17 33 37 47
05 06 12 14 22 34
03 20 35 41 48 49
08 12 17 24 25 45
03 10 18 22 29 45
13 16 22 23 39 43
03 10 17 31 36 45
02 24 32 37 43 49
10 16 27 28 29 42
02 35 38 39 40 48
08 23 34 36 44 47
09 16 21 26 28 29
10 15 32 33 39 41
04 11 20 33 43 45
01 08 21 22 27 36
04 12 25 36 43 49
09 12 23 26 34 43
19 23 24 30 34 46
08 17 20 21 31 45
04 06 17 27 36 48
07 08 19 28 32 41
13 17 26 30 39 47
10 15 20 29 32 43
13 19 22 25 27 45
04 09 19 21 25 42
01 12 24 37 44 47
08 19 31 34 38 42
11 14 31 33 34 47
19 22 32 33 38 48
08 13 14 20 37 40
03 08 10 13 41 47
01 02 14 16 26 30
30 34 36 37 41 49
01 02 14 17 22 37
03 09 28 34 39 47
01 07 09 15 35 37
13 14 19 24 32 33
01 09 10 24 31 41
11 14 19 23 35 41
25 26 27 31 42 49
14 16 21 26 37 48
11 22 24 34 35 40
05 10 12 13 15 17
05 12 18 22 44 45
September 20th, 2009 at 9:47 pm
Narend,
find missing numbers range 1-49 from each row or the whole range?
See this blog post: http://www.get-digital-help.com/2009/09/20/identify-missing-numbers-in-a-range-in-excel/