# 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?

• 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

1.....Car

2.....Plane

3.....Train

4.....Bike

5.....Helicopter

6.....<blank>

• Put this formula into each cell in Max Number column

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