Index Distinct Collect

Options
Jennifer Lindquist
Jennifer Lindquist ✭✭✭
edited 05/09/24 in Formulas and Functions

I have a sheet called PN List that has a checkbox column to signify which parts are packaged parts.

I'm creating a new sheet that gives me a list to be used in a dropdown for just packaged parts.

In the new sheet PN List - Pack, I have two columns 1 - PPN (column I want to fill), 2 - PaintHelper (checkbox column for collecting)

In the PN List - Pack, PPN column I have the formula:

=INDEX(DISTINCT(COLLECT({Part Number}, {Ck If Painted}, PaintHelper@row)), 1)

{Part Number} - Part number column on PN List sheet

{Ck If Painted} - Checkbox column on PN List sheet

PaintHelper@row - Checkbox column on PN List - Pack sheet. All the boxes in the rows are checked.

It used to work, now it doesn't. What am I missing?

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Jennifer Lindquist

    Your formula "=INDEX(DISTINCT(COLLECT({Part Number}, {Ck If Painted}, PaintHelper@row)), 1)" worked OK when I tested.

    You can get the same result without using the PaintHelper column by changing your formula to the following as the second sheet shows in the summary filed, PPN - One, as the PaintHelper@row is giving 'true' criteria to the {Ck If Painted} range;

    =INDEX(DISTINCT(COLLECT({Part Number}, {Ck If Painted}, 1)), 1)

    or in my demo dashboard

    =INDEX(DISTINCT(COLLECT(PartNumber:PartNumber, [Check If Painted]:[Check If Painted], 1)), 1)

    If you want all the distinct Paint Numbers whose Check If Painted columns are checked, you can use the formula in the PPN - ALL Formula field.

    =JOIN(DISTINCT(COLLECT(PartNumber:PartNumber, Check:Check, @cell = 1)), ", ")

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Jennifer Lindquist

    Your formula "=INDEX(DISTINCT(COLLECT({Part Number}, {Ck If Painted}, PaintHelper@row)), 1)" worked OK when I tested.

    You can get the same result without using the PaintHelper column by changing your formula to the following as the second sheet shows in the summary filed, PPN - One, as the PaintHelper@row is giving 'true' criteria to the {Ck If Painted} range;

    =INDEX(DISTINCT(COLLECT({Part Number}, {Ck If Painted}, 1)), 1)

    or in my demo dashboard

    =INDEX(DISTINCT(COLLECT(PartNumber:PartNumber, [Check If Painted]:[Check If Painted], 1)), 1)

    If you want all the distinct Paint Numbers whose Check If Painted columns are checked, you can use the formula in the PPN - ALL Formula field.

    =JOIN(DISTINCT(COLLECT(PartNumber:PartNumber, Check:Check, @cell = 1)), ", ")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!