List distinct values based on a criteria and remove blanks

I am making a metrics sheet that calculates what I need to show from a datasheet. The datasheet has columns for the Type (building type is either MOB or Hospital) and Departments (MOB has 12 departments and Hospital has 24 departments) along with a bunch of other columns that have data. In my formulas {CD - } is referencing the client datasheet.

On the metrics sheet, I am creating a distinct list of departments based on which type they are using this formula:

=IFERROR(INDEX(COLLECT({CD - Dept}, {CD - Type}, $Department$4, {CD - Dept}, INDEX(DISTINCT({CD - Dept}), IDX@row)), IDX@row), "")

Which gives me the list which is great, but there are "gaps" where that department IDX is one of the other types. I am building graphs from this and I don't want empty spaces in them. How can I get the list to stay dynamic and have no empty rows between the departments? (I know I can just type the IDX # out of order (1, 2, 3, 5, 7, etc.) and it will keep them together but I need it to stay dynamic as the datasheet can change order). @Paul Newcome this is sort of a piggyback on a question someone recently had.


Erin Hauser

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!