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

Veronika Young

@Paul Newcome Not yet, completely stumped, hoping you can help me in this thread 🤞

https://community.smartsheet.com/discussion/comment/291522#Comment_291522

Paul Newcome

@Veronika Young I have that thread on my list to take a look at when I have more than just a few minutes at a time. I am hoping to get to it this evening. My apologies for the delay.

Veronika Young

@Paul Newcome Thanks so much!

Jon Friend

I just wanted to say that I LOVE @Frank Falco and @Paul Newcome for sharing this trick. No idea how I would have pulled this off without your contributions above. 🤘

Jon Friend

I'm just wondering if there is any magic trick we can use to implement this solution as a column formula ? I've got the solution delivered by Paul working perfectly now, but my problem is that the sheet with the "count" column and the "number" column (using @Paul Newcome 's names) gets populated via a form... thus, I can't drag/drop that line-based formula into rows that don't exist yet. So, really need a "column formula" version of this. Is that at all possible??? I'm doing a workaround presently, but it's a mess.

Paul Newcome

@Jon Friend As long as you have at least two rows containing the formula above and/or below the new row, auto-fill should bring the formula(s) in for new entries that are made via form.

Josephine Camacho

@Paul Newcome The "number" and "count" column functions perfectly. However, when I add the formula for the "Distinct Name" formula, only the first row worked and once I converted the formula into a column formula, the rest of the column within Distinct Name says, "No Match". In the "Max Number" column, once again only the first row works and when I convert the formula to a column formula the rest of the column states "Invalid Operation". Do you have any suggestions on how get the rest of the column to function with the formula?

Paul Newcome

@Josephine Camacho Are you able to provide some screenshots as well as copy/paste the formulas exactly as they are in the sheet to here?

Josephine Camacho

@Paul Newcome Here is the post I created with screenshots and the formulas on my sheet. Thank you for your help!

https://community.smartsheet.com/discussion/83591/how-to-get-distinct-values-from-host-manager-name-and-active-contractors