Identify cell instance count

Options
Dave Schierman
edited 02/15/24 in Formulas and Functions

I’m looking for a way to identify a duplicate cell value in a row so I can mark it for removal to another sheet for archiving. Rows are entered via forms at the top of sheet that contain a “Product Name” and it’s status. I am referencing this from another sheet to find the current status of the product so I just need the latest entry for each product but would want to keep a few previous as well. Ideally, a formula that would denote 1st, 2nd, 3rd etc row instance.  Hopefully that makes sense.  Thanks

Best Answer

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @Dave Schierman

    Hope you are fine, please add a copy or screenshot after removing any sensitive data

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Dave Schierman

    I hope you're well and safe!

    Try something like this in a Checkbox or Flag type column.

    =IF(COUNTIF(ColumnName:ColumnName, ColumnName@row) > 1, 1)
    

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Dave Schierman
    Options

    Thank you much for your prompt responses. I don't think I explained myself well. Referencing the example screenshot, I'd like to calculate the "instance" column which will show the 1st, 2nd, 3rd, etc instance of the "Product" in the sheet.



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    My suggestion would be to insert an auto-number column with no special formatting.


    Then in a text/number column you can use...

    =COUNTIFS(Product:Product, @cell = Product@row, [Auto-Number]:[Auto-Number], @cell >= [Auto-Number]@row)

  • Dave Schierman
    Options

    Hi Paul,

    That did the trick. Thank you VERY much for your assistance.

  • Davism
    Options

    Hello

    I have a Smartsheet with an auto-number field which uses automation to copy the row 1, 2 or 3 times to a second Smartsheet work file.

    In the second Smartsheet file I want to count the instances of the Auto-Number field

    Auto-Number Instance

    ST01 1

    ST01 2

    ST01 3

    ST02 1

    In the instance column, how can I calculate the instance of the auto-number field ?

    I am doing this as the Payment Request on the first Smartsheet needs to be charged to be split and charged to three different people in the second Smartsheet.

    Hope you can help

    D

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @Davism Are you able to provide a screenshot for context?

  • Davism
    Options

    @Paul Newcome

    Thanks so much for getting back so quickly.

    A payment request is input into Smartsheet 1 and is auto-numbered e.g. ST000002 but needs to be split 2 or 3-ways.

    Accordingly the row ST000002 is copied 2 or 3 times into Smartsheet 2 (I think this is the only way that you can duplicate rows) where we want to give it an instance number e.g. 1, 2, 3 by counting how many times the auto-number appears.

    Eventually we would Join AutoNumber and Instance to give an ID like ST000002-1.

    We want an ID that reflects that there are three related items.

    How can I count the instances of the Auto-Number field ?

    When you copy the row from Smartsheet 1 to Smartsheet 2 it makes the AutoNumber field in Smartsheet 2 to be in the auto-number format of Smartsheet 1 and you can't add a second different auto-number type column

    Hope that's clear !

    Can you help ?

    This is Smartsheet 2 (the Instance field is manually input currently, we want it to count instances and be automatic)


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You would need to insert a system generated Created Date column in the second sheet then use something along the lines of

    =AutoNumber@row + "-" + COUNTIFS(AutoNumber:AutoNumber, @cell = AutoNumber@row, Created:Created, @cell<= Created@row)

  • Davism
    Options

    @Paul Newcome

    Thanks Paul for all your help

    Alas this doesn't appear to be working...

    Where instance contains the formula you suggested

    Do you know what is wrong ?

    thanks

    Davism

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    It is because each [AutoNumber] was copied over 3 times at the same exact time. Is that to be expected? I was under the impression there was a delay between each copy based on some status changing or something.

  • Davism
    Options

    Yes, each [Autonumber] is copied over at the same time

    A payment request is input into Smartsheet 1 and is auto-numbered e.g. ST000002 but needs to be split 3-ways.

    Accordingly the row ST000002 is copied 3 times into Smartsheet 2 (I think this is the only way that you can duplicate rows) where we want to give it an instance number e.g. 1, 2, 3 by counting how many times the auto-number appears.

    The three rows are copied by one automation in Smartsheet 1 and so appear in Smartsheet 2 at the same time.

    Is it possible to give them an instance number in Smartsheet 2 ?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    If you are already using an auto-number column and all three are being copied over at the same time, there is no way for us to further increment a count on them.

  • Davism
    Options

    Hello Paul

    Thanks for your help so far. I realised from the approach I was presenting that it might not be possible.

    So I changed some of my approach.

    I deleted Created Date from the input file and changed the Copy and Move logic so that instead of happening at the same time it happened at different times e.g.

    If the original input is to be split in 3 then copy at 0100 to the new file

    If the original input is to be split in 2 or 3 then copy at 0200 to the new file

    Move at 0300 to the new file (if to be split 1,2,3 ways i.e. every time)

    (so the input file is empty after 0300)

    This gave me

    ST000015 is split 2-ways, ST000014 is split 3 ways.

    Now I just need to add logic to only process transactions submitted the day before (to prevent someone inputting something at 0130 in the middle of the process and not not working properly)

    The formula you supplied worked like a dream once I changed my copy across process

    Many many thanks

    Davis

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!