Track if row has appeared in previous rows- the last few consecutive parent batches

Options
SYSPK
SYSPK ✭✭✭✭✭✭

I paste a few dozen rows onto a spreadsheet each week. We want to track if the client ID has been pasted for the last 3 consecutive weeks. It can be a box that gets checked. What's the best way to do this?

I already track which "batch" it's been sent out in because I manually label the parent rows and pull it into each child row (backend ".batch" column). It might involve subtracting the last three batches to equal 0 (batch #3 - batch #2 - batch #1 = 0) but I don't know how to specify a search within a parent "batch" above.

This duplicate detector formula may be on the right track but I don't know how to move forward.

=IF(COUNTIF(client:client, client@row) <> 1, 0, 1)


I appreciate any help. Thanks

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    You can eliminate your .Batch column by just placing

    =PARENT()

    in the Batch column for each child row.

    Move the Batch column just to the right of the Client column, and in a helper column "ClientBatch", join your Client and Batch together and convert them to a number value:

    =VALUE(JOIN(Client@row:Batch@row))

    Going by your example above, you'd have 123423, 123424, 123425, etc.

    Now, in the checkbox column:

    =IF(COUNTIF(ClientBatch:ClientBatch, OR(@cell = (ClientBatch@row - 2), @cell = (ClientBatch@row - 1))) = 2, 1, 0)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    You can eliminate your .Batch column by just placing

    =PARENT()

    in the Batch column for each child row.

    Move the Batch column just to the right of the Client column, and in a helper column "ClientBatch", join your Client and Batch together and convert them to a number value:

    =VALUE(JOIN(Client@row:Batch@row))

    Going by your example above, you'd have 123423, 123424, 123425, etc.

    Now, in the checkbox column:

    =IF(COUNTIF(ClientBatch:ClientBatch, OR(@cell = (ClientBatch@row - 2), @cell = (ClientBatch@row - 1))) = 2, 1, 0)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • SYSPK
    SYSPK ✭✭✭✭✭✭
    Options

    Thank you!!

    It gave me problems because it didn't recognize the ClientBatch field as numerical. I had to force it to be such and wrapped it all in an IFERROR for the non-client rows:


    =IFERROR(IF(AND(CONTAINS((VALUE(ClientBatch@row) - 1), ClientBatch:ClientBatch), CONTAINS((VALUE(ClientBatch@row) - 2), ClientBatch:ClientBatch)), 1),"")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!