For JOIN(DISTINCT(COLLECT formula, can you create exemptions?

Brittney Moffitt

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?

Paul Newcome

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)), ", ")

Paul Newcome

Are you able to provide your existing formula?

Brittney Moffitt

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?

Paul Newcome

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

Brittney Moffitt

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

Paul Newcome

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)), ", ")

Brittney Moffitt

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

Paul Newcome

Happy to help. 👍️

Brittney Moffitt

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.

Paul Newcome

Are you able to provide some updated screenshots for reference?

Brittney Moffitt

Screenshots of change in the formula/naming?

Paul Newcome

The data in the Department Number column please.

Brittney Moffitt

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.

Paul Newcome

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.

Brittney Moffitt

Thank you, Paul! That got me to a resolution!

Paul Newcome

Happy to help. 👍️