Finding Distinct Values from two different Sheets

Jake Gustafson
Jake Gustafson ✭✭✭✭✭✭
edited 11/21/22 in Formulas and Functions

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.

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    edited 11/21/22 Answer ✓

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • 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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Jake Gustafson
    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.
    Genevieve P. Employee
    edited 11/21/22 Answer ✓

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Jake Gustafson
    Jake Gustafson ✭✭✭✭✭✭

    Excellent. #PowerofCommunity

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

  • Jake Gustafson
    Jake Gustafson ✭✭✭✭✭✭

    This worked perfectly. Thanks.

  • Wonderful! Haha #PowerofCommunity as you said 🙂

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!