Help with Creating a formula to automate a check box that removes duplicates for certain criteria

JD_425

Looking for help with a formula to automate a check box that removes duplicates for certain criteria. Today have two formulas setup. One that pull together two fields and the other is a check box column with a formula that auto checks a box or leaves the box unchecked if there are duplicates. This doesn't meet all of our needs.

=IF(COUNTIF(String$1:String1, String@row) = 1, 1, 0)

*What we would like to have is a formula that joins the 3 column headers below (SMCC Action, Distribution Center and New Distrb Sku Nbr) and checks the box for all the line items where LI is reflected under SMCC Action but only checks the box once where NIR is reflected in SMCC Action column (essentially not checking the box for duplicate line items where NIR is listed). Not sure how to put together the formula so any help constructing would be greatly appreciated. Thank you!

Column Headers

SMCC Action (NIR, LI)

Distribution Center

New Distrb Sku Nbr

Paul Newcome

Can you describe your workflow a little more?

Where is the data coming from (manual entry, form, update request, etc)?

Where will new data be entered (top of sheet, bottom of sheet, inserted into middle of sheet, combination)?

Do you have a [Created Date] type system generated column to track when rows are entered? If so, do you want to keep the newest entry or the oldest entry?

JD_425

Hi Paul, thank you for the message. We have a multiple sheets setup that tracks our products that come into distribution with our various distributors in our Network. NIR represents an item new into distribution and we may have 3 current items with the volume to one new NIR item. Where the automated checkbox comes is we count the number of NIR requests by rolling up a report and don't want to count the NIR that may be listed 3 times but is really one item. Where it gets a little tricky is with the LI line items or what we call lock ins as we want to count those each time so want the checkbox to flag each of those. Essentially why inquiring about the question above if possible...does this help?    

JD_425

https://app.smartsheet.com/b/publish?EQBCT=50fc154d34b2414f869b64fc8edbfe54

Hi Paul, I added a link to a test version of the sheet. If you scroll to the right you can see the current formula in place which removes all the duplicates by only checking where unique. What would we like to have is to add in the SMCC Action column with NIR and LI so joining the 3 column headers together...

SMCC Action

Distribution Center

New Distrb Sku Nbr

Where NIR is noted in the string only the check the box where unique (essentially removing the duplicates)

Where LI is noted in the string check all boxes

Eric M Oliveira

Hello,

Happy to help, currently, we don’t have a method to join column headers, we can only join column values, but this will be considered as a possibility for future development.

To join all three column values you could add to your current formula in the String column to include the SMCC Action column. This formula could look like this. 

=[New Distrb Sku Nbr]@row + " " + [Distribution Center]@row + " - " + [SMCC Action]@row

Producing a result similar to this:

9561481 BWC Sysco San Francisco, Inc. NIR - NIR

To create a checkbox formula that checks the box for all the line items where LI is reflected under SMCC Action and only checks the box once where NIR is reflected in SMCC Action column (essentially not checking the box for duplicate line items where NIR is listed), this could look like this.

=IF(AND([SMCC Action]@row = "LI", COUNTIF([SMCC Action]:[SMCC Action], [SMCC Action]@row) >= 1), 1, IF(AND([SMCC Action]@row = "NIR", COUNTIF([SMCC Action]:[SMCC Action], [SMCC Action]@row) = 1), 1,0))

Be aware that is NIR occurs twice all values will be marked as 0. This is because currently, we do not have a method to create a formula and only check a box once with this type of workflow. You may be able to achieve this desired goal of only checking the box once for NIR by manually checking the first NIR box then utilizing this formula.

=IF(AND([SMCC Action]@row = "LI", COUNTIF([SMCC Action]:[SMCC Action], [SMCC Action]@row) >= 1), 1, IF(AND([SMCC Action]@row = "NIR", COUNTIF([SMCC Action]:[SMCC Action], [SMCC Action]@row) > 1), 0,0))

If you have any other questions on the above I would suggest connecting with us via this Support Team link, that way we can set aside a time for a screen share to achieve your desired goal. https://help.smartsheet.com/contact

Have a wonderful day. 

Cheers,

Eric

Smartsheet Technical Support

Paul Newcome

So you want ALL rows with "LI" to be checked and only duplicates for "NIR"?

Based on the data in the link you provided, it looks like you are already checking the boxes for unique entries.