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
-
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(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>
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
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!
-
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(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>
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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!
-
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
-
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,
-
@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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Juraj Slezak Are you getting an error or an incorrect result? What exactly are you trying to accomplish with your formula?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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?
-
@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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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.
-
@Andy Trickett Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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
-
@Veronika Young It depends. How exactly are you using it?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome it turned out to be a limitation of 4,000 characters in the cell, not an issue with the formula.
-
@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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!