Finding Distinct Values from two different Sheets

Jake Gustafson

Hello, I have a scenario where I'd been successful in summarizing the distinct users names who'd created entries in my sheet and done some calculations from that. Below is my formula:

=IFERROR(INDEX(DISTINCT({NCP Originator}), Index@row), "")

Now, my records have gotten to a point where I needed to archive older records, but now I have two source sheets that I'd like to summarize the Distinct list of users who originated the records from both sheets. Is that possible to combine into what I have with the formula above?

{NCP Originator} is from my live sheet

{NCP Originator ARCHIVE} is from my archive sheet

I want to be able to get a unique/distinct list of names across both those columns.

Help me Smartsheet Community, you're my only hope.

Genevieve P.

Ah thank you for clarifying, @Jake Gustafson!

Ok, my next thought is to have two separate columns: one for each sheet. Then in a third column you can reference both columns as one range to do a third Index(Distinct, like so:

I've highlighted the two names that are repeats in the two sheets, so we can make sure they don't repeat in the combined column.

=IFERROR(INDEX(DISTINCT([First Sheet]:[Second Sheet]), [Row Reference]@row), "")

You could then hide the two helper columns if needed, or use them for other calculations (e.g. to see if they appear in the current sheet or in the archive sheet).

Let me know if this works 🙂

Cheers,

Genevieve

Genevieve P.

Hey @Jake Gustafson

Before we go down the formula route, can I check to see if a Report would work for you?

You could use the two sheets as the 2 sources for your Row Report. Then Group by the Names (as long as it's a text/number column or a single dropdown list). You could keep the Report collapsed so you only see the names, and apply a Summary if you needed to see the unique count.

This would also allow you to expand rows if you did want to view each individual's rows across both sheets as well. Let me know your thoughts on this!

Cheers,

Genevieve

Jake Gustafson

@Genevieve P. certainly the Report option is feasible and in some cases like this I've done that, but I'm pulling together some other stats based on the unique values of names. I rank the frequency of their submissions by the facility they submitted requests for, then I pull those top-ranking records into a Report to use for a Donut/Pic Chart.

I'm open to suggestions, I like to try reducing the number of Sheets that are needed (especially with my name on them), but I can't always turn a Report into specifically what I'm looking for. I've run into issues before with too many records for the Report to hold as well, which at this point, I'm not filtering out any data, simply using everything that's available.

Genevieve P.

Ah thank you for clarifying, @Jake Gustafson!

Ok, my next thought is to have two separate columns: one for each sheet. Then in a third column you can reference both columns as one range to do a third Index(Distinct, like so:

I've highlighted the two names that are repeats in the two sheets, so we can make sure they don't repeat in the combined column.

=IFERROR(INDEX(DISTINCT([First Sheet]:[Second Sheet]), [Row Reference]@row), "")

You could then hide the two helper columns if needed, or use them for other calculations (e.g. to see if they appear in the current sheet or in the archive sheet).

Let me know if this works 🙂

Cheers,

Genevieve

Paul Newcome

I second @Genevieve P.'s suggestion. Pulling in each list and then consolidating.

Jake Gustafson

Excellent. #PowerofCommunity

I'll try that out after a bit and keep you posted, Thanks.

Jake Gustafson

This worked perfectly. Thanks.

Genevieve P.

Wonderful! Haha #PowerofCommunity as you said 🙂