How to get distinct values from "Host Manager Name" and "Active Contractors"?
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:
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))
Answers
-
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))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!