Identify cell instance count

Dave Schierman

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

Paul Newcome

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)

Bassam Khalil

Hi @Dave Schierman

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

Andrée Starå

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!

Dave Schierman

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.

SSexample.pdf

Paul Newcome

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

Hi Paul,

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

Paul Newcome

Happy to help. 👍️

Davism

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

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

Davism

@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

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

@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

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

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

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

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