Need help pulling distinct count from another sheet

Sherry Hamilton

Hi. I am creating a rollup sheet using two separate directories imported onto separate sheets. Looking for Unique NPI and Unique locations.

The following formula works for the first entry, but is returning 1 for the next 3. =COUNT(DISTINCT(COLLECT({DE Location ID}, {DE Benefit Name}, "vision", {DE Client Name}, CONTAINS([Client Name]@row, @cell))))

=COUNT(DISTINCT(COLLECT({DC Location ID}, {DC Benefit Name}, "vision", {DC Client Name}, CONTAINS([Client Name]@row, @cell)))). The only difference is that the DC information is coming from the same directory but has 3 different clients. The NPI count seems to working fine.

Any suggestions?

Paul Newcome

Yes and Yes. The leading zero creates a text string instead of a number. Inconsistent data types will cause issues.

insert a new text/number column and use this as a column formula:

=[Location ID]@row + ""

Now everything will be converted to a text string for consistent data type, and you can reference this column in your cross sheet reference.

Paul Newcome

When you say that it has "3 different clients" do you mean that there are three options selected in a multi-select dropdown?

Sherry Hamilton

Hi Paul,

Yes, there are 3 different options.

Paul Newcome

What kind of data is in the {DE Location ID} range? Do some cells have numbers and other cells have text?

Sherry Hamilton

Hi Paul,

I looked at DC and DE location ID range and both have numbers only (5-7 digits).

Thanks,

Sherry

Sherry Hamilton

Paul,

One of the Location IDs in the DC data does start with 0. Could that be the issue? If so, is there a way around it?

Sherry

Paul Newcome

Yes and Yes. The leading zero creates a text string instead of a number. Inconsistent data types will cause issues.

insert a new text/number column and use this as a column formula:

=[Location ID]@row + ""

Now everything will be converted to a text string for consistent data type, and you can reference this column in your cross sheet reference.

Sherry Hamilton

That worked! Thank you so much!

Paul Newcome

Happy to help. 👍️