How to get distinct values from "Host Manager Name" and "Active Contractors"?

Josephine Camacho

I am trying to consolidate the "Host Manager Name" column to state the name once in the "Distinct Name" column. I would like the same for the "Active Contractors" column, to state the number once in the Max Number Column.

I followed the instructions from the link below:

https://community.smartsheet.com/discussion/67208/how-to-get-distinct-values-from-a-column-then-shows-their-maximum-number

It worked for the first row, but as I dragged the formula down, I got a "No Match" and "Invalid Operation". I also could not convert the formula in the "Count" Column into a column formula because the system said there was an issue with the syntax.

The formula in the "Count" Column is:

=IF(COUNTIFS([Host Manager Name]$2:[Host Manager Name]@row, [Host Manager Name]@row) = 1, COUNT(DISTINCT([Host Manager Name]@row)))

The formula in the "Distinct Name" Column is:

=INDEX([Host Manager Name]:[Host Manager Name], MATCH(Number@row, Count:Count, 0))

The formula in the "Max Number" column is:

=MAX(COLLECT(Number:Number, [Host Manager Name]:[Host Manager Name], [Distinct Name]@row))

Paul Newcome

My suggestion would be to insert an auto-number column with no special formatting (called "Auto") and a text number column (called "Row" with the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Then to get the first entry of each name...

=IF(COUNTIFS(Row:Row, @cell <= Row@row, [Host Manager Name]:[Host Manager Name], @cell = [Host Manager Name]@row) = 1, [Host Manager Name]@row)

Then to pull the number in:

=IF([Distinct Name]@row <> "", MAX(COLLECT(Number:Number, [Host Manager Name]:[Host Manager Name], @cell = [Distinct Name]@row))