List distinct values based on a criteria and remove blanks

Erin Hauser

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.

Thanks!

Erin Hauser

Paul Newcome

Try an INDEX/DISTINCT/COLLECT instead.

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

Paul Newcome

Try an INDEX/DISTINCT/COLLECT instead.

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

Erin Hauser

Perfect! I figured it was the order I had it in, I just couldn't get it quite right. Here is the final result. Thank you so much for your help!!!

Paul Newcome

Happy to help. 👍️