Formula based on matching Primary Column Values

Ryan Jezierski

We use smartsheet for production tracking in beverage manufacturing. The primary column we use to create a batch ID for the associated production run: Date Produced + Brand Code + Run sequence (A,B,C, etc.) + Tank #. So an example would be: 03/15/21-RWO-A-01. RWO produced on 3/15, first run of the day and tank # 01. We end up with a lot of form entries tied to that batch ID, if I wanted to have a sequential number for each row added with the same Primary column value is there a way to do that without indenting to create a parent row?

Bassam Khalil

Hi @Ryan Jezierski

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Paul Newcome

Which number are you wanting to make sequential? Are the form entries going on the same sheet as the initially created batch ID? Are you able to provide some screenshots with mock data that shows your setup as well as what you are trying to accomplish?

Ryan Jezierski

So here you can see the batch IDs. All data is coming from form entry at each piece of equipment on our canning line. So information from our depalletizer, can filler, cartoning machine, & case packing machine all link to this one sheet. They are collecting information hourly throughout the production run, and we are able to associate which production run the data is associated with by the batch ID created in the primary column.

What I would like to create is sequential numbering for each pallet of empty cans added to the depalletizer that have the same batch ID. This links to a report on a dashboard so the operator at the depalletizer can keep track of how many pallets have been put on the depalletizer for the particular production run.

Paul Newcome

Ok. So I am assuming that [Production Run] is what you want to increment. How are you wanting the incrementation to look? Are you wanting to increment the "01" that is currently on the end of the string, or are you wanting to add an additional portion to the string?

Ryan Jezierski

The 01 on Production run is the tank # that the product was pulled from. Ideally the sequential number would be in a separate column just for the purpose of tracking can pallets for the associated production run.

Paul Newcome

Ok. In a separate column you would use something along the lines of...

=COUNTIFS([Production Run]$1:[Production Run]@row, [Production Run]@row)