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

Michael Giovanni · 04/07/20

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

Paul Newcome · 04/07/20

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>

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(Number@row, 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(Number@row, 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>

Frank Falco · 04/07/20

Put this formula into each cell in Max Number column

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

Paul Newcome · 04/07/20

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>

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(Number@row, 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(Number@row, 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>

Gordon · 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!

Jeff Bunker · 01/03/21

https://community.smartsheet.com/discussion/comment/245398#Comment_245398

@Paul Newcome

I'm using the first helper column to create numbers for distinct entries in a column, It does well for anything that has text in front of it, but skips rows with # only that are unique. Is there a way to fix this to grab those as well?

For example SWFZ160493 will get a # , but 111152 will be skipped. It is showing as a string and not a value.

Thanks,

Jeff

Juraj Slezak · 02/09/21

Hi There,

I can't get it work.

What is wrong with my formula?

=IF(COUNTIFS(Description2045:Description2054, Description@row) = 1, COUNT(DISTINCT(Description2045:Description2054)))

Thanks for answer,

Paul Newcome · 02/09/21

@Jeff Bunker Create a text/number column and then use this as a column formula:

=[Column name]@row

Change [Column Name] to reflect whichever column contains "SWFZ160493" and "111152".

Then reference this new column in your formula that generates your numbers.

The problem you are running into is that your data is not all the same format. You have some numbers and some text. All data values must be the same. Using this helper column converts everything to a text string and should get the job done for you.

Paul Newcome · 02/09/21

@Juraj Slezak Are you getting an error or an incorrect result? What exactly are you trying to accomplish with your formula?

Andy Trickett · 03/26/21

https://community.smartsheet.com/discussion/comment/245398#Comment_245398

@Paul Newcome this has greatly helped me towards achieving a sheet listing distinct values (customer names in my example). I have adapted your example to show the SUM not MAX . My challenge is that my master sheet has to be populated by smartsheet 'Forms' hence I the 'help column' or 'Number column' for me, cannot ne prepopulated manually as you describe "

I have tried to make the properties of my 'Number' help column as Auto-number, but sadly that stops the formula from creating the distinct /unique value (customer name).

Any suggestions please?

Paul Newcome · 03/26/21

@Andy Trickett Are you able to move the distinct list starting with the Number column to a separate sheet? That would allow you to prefill as many rows as you need to.

Andy Trickett · 03/30/21

@Paul Newcome Yes, a really helpful nudge in the right direction thank you. I have created a new sheet solely to hold my growing 'DISTINCT' list, alongside my 500 rows of prepopulated consecutive NUMBERS (help column) Which I actually used the sheets PIMARY COLUMN for. I left the COUNT (help column) in the 'Master sheet' which has the smartsheets Form feeding it. All work great & KPIs shown in a Dashboard. 😀 thanks again.

Paul Newcome · 03/31/21

@Andy Trickett Happy to help. 👍️

Veronika Young · 05/14/21

Are there any limitations on the "distinct" formula? I'm using it in smartsheets and seems to stop pulling in distinct data after row 273

Paul Newcome · 05/17/21

@Veronika Young It depends. How exactly are you using it?

Veronika Young · 05/18/21

@Paul Newcome it turned out to be a limitation of 4,000 characters in the cell, not an issue with the formula.

Paul Newcome · 05/18/21

@Veronika Young That was going to be my first guess. I am glad you got it figured out. Were you able to implement a solution?

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

Best Answer

Answers

Help Article Resources

Categories