How to get distinct values from a column, then shows their maximum number?

How to get distinct values from a column, then shows their maximum number?

How to create a formula that can get me the distinct name list from the left table then get their max values?

Please help me

Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    To get the list, you are going to want to use two helper columns. The first I will call "Count" and the second I will call "Number".

    In Count:

    =IF(COUNTIFS([List of Vehicles]$1:[List of Vehicles]@row, [List of Vehicles]@row) = 1, COUNT(DISTINCT([List of Vehicles]$1:[List of Vehicles]@row)))


    This will number each of the distinct values at their first entry in the [List of Vehicles] column. Using your above screenshot as an example, it should look something like:

    1

    2

    3

    4

    <blank>

    <blank>

    <blank>

    5

    <blank>


    Then in the Number column, you would manually enter

    1

    2

    3

    4

    5


    Your [Distinct name] column would then contain the formula of

    =INDEX([List of Vehicles]:[List of Vehicles], MATCH([email protected], Count:Count, 0))


    Dragfill that down, and you should be set. You will need to manually add more numbers in the Number column as new vehicle types are added.

    You could pre-fill additional numbers to anticipate additional vehicle types and wrap the INDEX/MATCH in an IFERROR to leave those cells blank until they are needed.

    =IFERROR(INDEX([List of Vehicles]:[List of Vehicles], MATCH([email protected], Count:Count, 0)), "")


    So if the setup is

    1.....f

    2.....f

    3.....f

    4.....f

    5.....f

    6.....f


    Your screenshot data would show

    1.....Car

    2.....Plane

    3.....Train

    4.....Bike

    5.....Helicopter

    6.....<blank>

Answers

  • Put this formula into each cell in Max Number column

    =MAX(COLLECT(Number:Number, [List of Vehicles]:[List of Vehicles], [Distinct name]@row))


    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

  • GordonGordon
    edited 06/29/20

    @Frank Falco Thank you for the formula! As an add on, I converted MAX to SUM, and added an IF logic as a prefix, to only add totals where the Distinct Name was completed in the summary table


    Works perfectly!

Sign In or Register to comment.