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

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

Jeff Reisman

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)