Track if row has appeared in previous rows- the last few consecutive parent batches
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
-
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
-
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 142 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!