For JOIN(DISTINCT(COLLECT formula, can you create exemptions?
I am trying to use a JOIN(DISTINCT(COLLECT formula that is working to target all the items in the range; however, I am needing to focus the data to show only three of the items from that range OR to create an exemption to remove one specific item from the range. Is this possible? Or is another formula needed to capture an exemption?
Best Answer

In that case you would need to set up a range/criteria set for the department (range) being "not equal to" (criteria) that specific one.
=JOIN(DISTINCT(COLLECT({2023 Department}, {2023 Department}, @cell <> "Specific Department", {2023 POG Range 2}, true, {2023 OBF Vendor Range 1}, Vendor@row)), ", ")
Answers

Are you able to provide your existing formula?

Hey Paul!
The existing formula:
=JOIN(DISTINCT(COLLECT({2023 Department}, {2023 POG Range 2}, true, {2023 OBF Vendor Range 1}, Vendor@row)), ", ")
This is the working formula to cover all departments. I have tried a number of other formulas to create an exemption that used Contains(Not(, only Not(. As well as trying to specifically call out the three departments I wanted to use with an OR( statement. Would you like to see those?

And what exactly are you trying to remove from which range?

I am trying to remove one specific department from being viewed from the departments.

In that case you would need to set up a range/criteria set for the department (range) being "not equal to" (criteria) that specific one.
=JOIN(DISTINCT(COLLECT({2023 Department}, {2023 Department}, @cell <> "Specific Department", {2023 POG Range 2}, true, {2023 OBF Vendor Range 1}, Vendor@row)), ", ")

Thank you so much Paul! That was what I needed!

Hey @Paul Newcome
Would I be able to get additional insight and help on this matter? After changing the department to a Number value with no letters, it is missing information.
So the original formula recommended: =JOIN(DISTINCT(COLLECT({2023 Department}, {2023 Department}, @cell <> "Specific Department", {2023 POG Range 2}, true, {2023 OBF Vendor Range 1}, Vendor@row)), ", ")
In the original formula "Specific Department" was "### DeptName" (so, department number a space department name. With the changes we only have the department number.
I used the same formula, but now it is:
=JOIN(DISTINCT(COLLECT({2023 Department}, {2023 Department}, @cell <> "DepartmentNumber", {2023 POG Range 2}, true, {2023 OBF Vendor Range 1}, Vendor@row)), ", ")
The "DepartmentNumber" is only numeric values and one word causing the formula to only show two departments and not all. I tried removing the " " from the numeric value and the formula only pulled the other half of the information.
I apologize if this is beyond confusing, but I am not sure why the formula is only giving half the information now that it changed to a numeric value.

Are you able to provide some updated screenshots for reference?

Screenshots of change in the formula/naming?

The data in the Department Number column please.

Here it the view from the original source that the Department Number pulls from:
The grid that the information is pulling into:
There should be four different departments showing in the grid from above, but it is only showing those two.
The formula: =JOIN(DISTINCT(COLLECT({Source Dept}, {Source Dept}, @cell <> "218", {Source Range 2}, true, {Source Range 3}, Vendor@row)), ", ")
Originally, this is what we were seeing for the department information and how it would pull through into the previous grid:
I hope this helps. Please let me know if more insight is needed.

The problem is that some of the department numbers are actual numbers and some are text values (leading zeros). Inconsistent data types can mess things up pretty quickly. Insert a text/number column on the source sheet and use a column formula to convert all to text.
=[Column name]@row + ""
Then reference this column in formulas.

Thank you, Paul! That got me to a resolution!
Help Article Resources
Categories
Check out the Formula Handbook template!