Create a list of majority values

Question

I have the following Excel spreadsheet:

            A                     B   
    1    Values         Descending order of majority values
    2      50                     80 (--> Appears 6 times in Column A)
    3      80                    -10 (--> Appears 6 times in Column A)
    4     -10                     50 (--> Appears 3 times in Column A)
    5     -10                     70 (--> Appears 1 time in Column A)
    6     -10                     40 (--> Appears 1 time in Column A)
    7      40 
    8      80                 
    9     -10   
   10      80
   11     -10   
   12      50
   13      80
   14      50
   15      70
   16      80
   17      80
   18     -10
   19

In Column A I have a list of values.
In Column B I want to list the values based on their majority in the list.

The only formla I could find so far is the one from here:

={MAX(MODE.MULT(A1:A18))}

This formula would give me the value with the highest majority which is in this case 80 but I cannot create a list out of it.

Do you have any idea of a formula that could create such a list?

NOTE:

a) Values in Column A can have the same majority. If this is the case I want that both values are listed in Column B (In this case 80 and -10)

b) Values in Column A can be positive or negative.

Did you try something like this [Find the top 10 values in an Excel range without sorting](https://www.techrepublic.com/article/find-the-top-10-values-in-an-excel-range-without-sorting/)? — Pᴇʜ, Sep 28 '18 at 06:52
A [Pivot Table](https://support.office.com/article/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576) is probably the easiest. — ashleedawg, Sep 28 '18 at 06:57
@PEH: My question is not a duplicate of the one you mentioned! In the question you mentioned a list of the MAX-values should be created. My question is about the MAJORITY-value which means that my question is about how OFTEN a value appears in the list and not how HIGH the value itself is! — Michi, Sep 28 '18 at 06:57
Another resource you would find at Google: [List most frequently occuring numbers](https://exceljet.net/formula/list-most-frequently-occuring-numbers) — Pᴇʜ, Sep 28 '18 at 07:07
@Michi if you are after "how often" then you should check out the frequency() function. — Solar Mike, Sep 28 '18 at 08:06

score 0 · Accepted Answer · answered Sep 28 '18 at 08:49

Put this array formula in the first cell and drag down:

=LARGE(IF(FREQUENCY($A$2:$A$18,$A$2:$A$18)=LARGE(FREQUENCY($A$2:$A$18,$A$2:$A$18),ROW(1:1)),$A$2:$A$18),SUMPRODUCT(--(LARGE(FREQUENCY($A$2:$A$18,$A$2:$A$18),ROW($A$1:INDEX(A:A,ROW(1:1))))=LARGE(FREQUENCY($A$2:$A$18,$A$2:$A$18),ROW(1:1)))))

Being an array formula it must be confirmed with Ctrl-Shift Enter instead of Enter when exiting Edit Mode

Create a list of majority values

1 Answers1